SQL Server Performance Forum – Threads Archive

How can I return output param. with Dynamic Sql?

I want to return @TotalRegisters parameter to web app, but I don’ t know how to do it. I tried this: (But it doesn#%92 t work, I receive always @TotalParameters = Null)
USE market
GO
ALTER PROCEDURE test_dyn2
@TotalRegisters nvarchar(100) output
As BEGIN
SET NOCOUNT ON
Declare @sql nvarchar(4000), @param_list nvarchar(4000) SELECT @sql =
‘ SELECT ‘ + @TotalRegisters + ‘ = COUNT(*)
FROM Offers’
SELECT @param_list = ‘@TotalRegisters nvarchar(100) output’ EXEC sp_executesql @sql, @param_list, @TotalRegisters output
SET NOCOUNT OFF
END
GO
Thank you,
Cesar

Finally I figured it out:
USE market
GO
ALTER PROCEDURE test_dyn2
@TotalRegisters nvarchar(100) output
As
BEGIN
SET NOCOUNT ON
Declare @sql nvarchar(4000),
@param_list nvarchar(4000) SELECT @sql = ‘ SELECT @TotalRegisters = COUNT(*) FROM Offers’
SELECT @param_list = ‘@TotalRegisters nvarchar(100) output’ EXEC sp_executesql @sql, @param_list, @TotalRegisters output SET NOCOUNT OFF
END
GO

Yes! Now it works [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]. Thank you! I already have one more piece of the puzzle. Do you know how to handle parameters inside Dynamic Sql declaration? I mean for example, set @Data and @Id variables to its values and use them later in the same Dynamic Sql declaration (See the example part in bold) <pre id="code"> USE market GO ALTER PROCEDURE test_dyn4 @TotalRegisters nvarchar(100) output As BEGIN SET NOCOUNT ON DECLARE @sql nvarchar(4000), @param_list nvarchar(4000) DECLARE @offerDate As datetime DECLARE @Id As nvarchar(1000) SELECT @sql = ‘ SELECT convert(char(23), @offerDate, 126) = offe.offerDate, @Id = offe.Offer_id From Offers As offe WHERE 1 = 1′ SELECT @sql = @sql + ‘ Order by offe.offerDate Desc, offe.Offer_id Desc’ SELECT @sql = @sql + ‘ SELECT offe.Offer_id, offe.User_num, offe.offerDate, offe.Offer_title From Offers As offe WHERE 1 = 1 AND ((offe.offerDate < convert(char(23), @Data, 126) ) Or (offe.offerDate = convert(char(23), @Data, 126) And offe.Offer_id <= @Id))’ SELECT @sql = @sql + ‘ Order by offe.offerDate Desc, offe.Offer_id Desc’ SELECT @sql = @sql + ‘ SELECT @TotalRegisters = COUNT(*) FROM Offers As offe WHERE 1 = 1′ SELECT @param_list = ‘@offerDate datetime output, @Id nvarchar(1000) output, @TotalRegisters nvarchar(100) output' EXEC sp_executesql @sql, @param_list, @offerDate output, @Id output, @TotalRegisters output SELECT @Data, @Id SELECT @TotalRegisters SET NOCOUNT OFF END GO</pre id="code"> But this doesn#%92 t work

May I ask you before looking at your code, why are you using dinamic sql for that? I don’t see anything that can’t be done with normal sql.

It doesn#%92 t matter, thank you for your aid in my first question!

quote: why are you using dinamic sql for that? I don’t see anything that can’t be done with normal sql.

That was only a little example of my complete query, if you want to see why I may need Dynamic Sql look at this recent posthttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10977 . This is only a third part or less of complete â€˜Where#%92 clause I need. The user can search offers specifying whatever combination among 30 parameters, and this can be complicated to handle with static Sql… Also, look at this articlehttp://www.sommarskog.se/dyn-search.html . Those are the main reasons why I am trying to write my code with Dynamic Sql

I’ m sorry, the post I pointed to was:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10829

quote:
SELECT @sql = ‘ SELECT convert(char(23), @offerDate, 126) = offe.offerDate, @Id = offe.Offer_id
From Offers As offe
WHERE 1 = 1′

You can’t assign a value to an expression. You have to assign to variable. Why do you need that conversion?

You are right, now it works! And I have all the pieces of the puzzle [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] I am going to design the complete query and see the performance. Thank you very much, Cesar

]]>