SQL Server Performance Forum – Threads Archive

Starting with Dynamic SQL (Select problem)

I have a dynamic SQL SP that its â€˜Select#%92 statement stars thus:
Select @sql =
‘Declare @offer_date As datetime, @Id As bigint
SELECT @offer_date = offe.offer_date, @Id = offe.Offer_id
From Offers As offe …
And I receive an error from app that says: You must declare the variable â€˜@offer_date#%92. And I already declared it.. What is wrong? Thanks

Hi Cesar,
When you declare any variable inside dynamic statement its not accesible after executing the code.
To access that you need to use sp_executesql and then with output parameter

What is the purpose of using Dynamic SQL in your case?
Post your exact requirement Refer here more on Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html Madhivanan Failing to plan is Planning to fail

Dynamic SQL is used if your are querying tables on a fly & your not sure which column,tables are going to be involved. It is like one command & any variable declared in dynamic sql can be used only inside the dynamic sql query. You can declare the variable in the SP & then pass only the values to dynamic SQL.

Cesar,
Consider this example: declare @statement nvarchar(1000)
declare @Top5Sum int set @statement=N’select @Top5Sum=sum(bytes) from ‘[email protected] exec sp_executesql @statement,N’@Top5Sum int OUTPUT’,@Top5Sum OUTPUT select @Top5Sum

Check out SQL Server MVP Erland Sommarskog’s in-depth article on the topic. It should answer almost all questions. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Thank you ranjitjain, I tried to do what you said but I can#%92 t achieve it. Madhivanan, you recommended to me a very interesting article in this post:http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10712 . And after read it I though it was the best option in that case.
Thank you again for this another interesting article!
Here is the structure of my SP to find first row of results: (After that, I have more code to get all rows and return a specified number of records per page to web application) USE market3
GO
ALTER PROCEDURE findOf_dyn
@CurrentPage int, @PageSize int, @Family smallint, @Product smallint, ..
As BEGIN
SET NOCOUNT ON Declare @sql nvarchar(4000), @param_list nvarchar(4000),
@Max_row int, @First_row int SET @Max_row = @PageSize * @CurrentPage
SET @First_row = @Max_row – (@PageSize – 1) SET ROWCOUNT @First_row Select @sql =
‘Declare @Offer_date As datetime, @Id As bigint
SELECT @Offer_date = offe.Offer_date, @Id = offe.Offer_id
From Offers As offe LEFT JOIN Products_name As prod
On offe.Product_num = prod.Product_Id
LEFT JOIN Families_product As fam
On prod.Family_num = fam.Family_Id
LEFT JOIN Offers_quality As of_qual
On offe.Offer_id = of_qual.Offer_num
LEFT JOIN Offers_rippening As of_rip
On of_qual.Offer_qual_id = of_rip.Offer_qual_num WHERE 1 = 1′
If @Family <> 0
SELECT @sql = @sql + ‘ AND (fam.Family_Id = @xFamily Or ([email protected] = 9 AND offe.Other_families IS NOT NULL))’ If @Product <> 0
SELECT @sql = @sql + ‘ AND offe.Product_num = @xProduct’ SELECT @sql = @sql + ‘ Order by offe.Offer_date Desc, offe.Offer_id Desc’ SELECT @param_list = ‘ @xCurrentPage int, @xPageSize int, @xFamily smallint, @xProduct smallint, …’ EXEC sp_executesql @sql, @param_list, @CurrentPage, @PageSize, @Family, @Product, â€¦
…
What I am trying to accomplish here is (among others) set @Offer_date = offe.Offer_date and @Id = offe.Offer_id in this dynamic SQL Select:
…
Select @sql =
‘Declare @Offer_date As datetime, @Id As bigint
SELECT @Offer_date = offe.Offer_date, @Id = offe.Offer_id
From Offers As offe LEFT JOIN Products_name As prod
…
In order to use these variables later (in the same SP) here (in bold):
SET ROWCOUNT @PageSize Select @sql =
‘SELECT offe.Offer_id, offe.User_num, offe.Offer_date, offe.Offer_title, … From Offers As offe LEFT JOIN Products_name As prod
On offe.Product_num = prod.Product_Id
LEFT JOIN Families_product As fam
On prod.Family_num = fam.Family_Id
LEFT JOIN Offers_quality As of_qual
On offe.Offer_id = of_qual.Offer_num
LEFT JOIN Offers_rippening As of_rip
On of_qual.Offer_qual_id = of_rip.Offer_qual_num
WHERE 1 = 1 AND ((offe.Offer_date < @Offer_date) Or (offe.Offer_date = @Offer_date And offe.Offer_id <= @Id))’ If @Family <> 0
SELECT @sql…
…
How can I do it?

I think the solution is this: (In bold) At least none error is returned from app.. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] <pre id="code"> USE market3 GO ALTER PROCEDURE findOf_dyn @CurrentPage int, @PageSize int, @Family smallint, @Product smallint, .. As BEGIN SET NOCOUNT ON Declare @sql nvarchar(4000), @param_list nvarchar(4000), @Max_row int, @First_row int SET @Max_row = @PageSize * @CurrentPage SET @First_row = @Max_row – (@PageSize – 1) SET ROWCOUNT @First_row Declare @Offer_date nvarchar(1000), @Id nvarchar(1000) Select @sql = ‘SELECT#%92 + @Offer_date + â€˜= offe.Offer_date,#%92 + @Id + â€˜= offe.Offer_id From Offers As offe LEFT JOIN Products_name As prod On offe.Product_num = prod.Product_Id LEFT JOIN Families_product As fam On prod.Family_num = fam.Family_Id LEFT JOIN Offers_quality As of_qual On offe.Offer_id = of_qual.Offer_num LEFT JOIN Offers_rippening As of_rip On of_qual.Offer_qual_id = of_rip.Offer_qual_num WHERE 1 = 1′ If @Family <> 0 SELECT @sql = @sql + ‘ AND (fam.Family_Id = @xFamily Or ([email protected] = 9 AND offe.Other_families IS NOT NULL))' If @Product <> 0 SELECT @sql = @sql + ‘ AND offe.Product_num = @xProduct' SELECT @sql = @sql + ‘ Order by offe.Offer_date Desc, offe.Offer_id Desc’ SELECT @param_list = ‘ @xCurrentPage int, @xPageSize int, @xFamily smallint, @xProduct smallint, @Offer_date nvarchar(1000) output, @Id nvarchar(1000) output …' EXEC sp_executesql @sql, @param_list, @CurrentPage, @PageSize, @Family, @Product, @Offer_date output, @Id output … </pre id="code"> And later: <pre id="code"> … WHERE 1 = 1 AND ((offe.Offer_date <#%92+ @Offer_date +#%92) Or (offe.Offer_date =#%92 + @Offer_date +#%92 And offe.Offer_id <=#%92 + @Id +#%92))' </pre id="code"> The problem now is none record is returned in any case

Why did you use nvarchar for Date? Use this Declaration Declare @Offer_date DateTime, @Id nvarchar(1000) Madhivanan Failing to plan is Planning to fail

Because if I declare @Offer_date as datetime, thus:
Declare @Offer_date datetime, @Id nvarchar(1000) Select @sql =
‘SELECT#%92 + @Offer_date + â€˜= offe.Offer_date,#%92 + @Id + â€˜= offe.Offer_id
From Offers As offe
I receive an error from application that says: error when convert a string to datetime. I suppose this is because all code generated inside @sql parameter becomes a string (is a string).

I see that it lacked (SELECT @Offer_date, @Id) after (EXEC sp_executesql), thus: <pre id="code"> EXEC sp_executesql @sql, @param_list, @CurrentPage, @PageSize, @Family, @Product, @Offer_date output, @Id output SELECT @Offer_date, @Id </pre id="code"> And now I receive next error [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] Here is my complete SP: It#%92 s a SP for paging through results <pre id="code"> USE market3 GO ALTER PROCEDURE findOf_dyn @CurrentPage int, @PageSize int, @Family smallint, @Product smallint, @TotalRecords nvarchar(100) output As BEGIN SET NOCOUNT ON Declare @sql nvarchar(4000), @param_list nvarchar(4000), @Max_row int, @First_row int SET @Max_row = @PageSize * @CurrentPage SET @First_row = @Max_row – (@PageSize – 1) Declare @Offer_date datetime, @Id nvarchar(1000) Select @sql = ‘SET ROWCOUNT#%92 + convert(nvarchar(100), @First_row) + â€˜ SELECT#%92 + convert(nvarchar(100), @Offer_date) + â€˜= offe.Offer_date,#%92 + @Id + â€˜= offe.Offer_id From Offers As offe LEFT JOIN Products_name As prod On offe.Product_num = prod.Product_Id LEFT JOIN Families_product As fam On prod.Family_num = fam.Family_Id LEFT JOIN Offers_quality As of_qual On offe.Offer_id = of_qual.Offer_num LEFT JOIN Offers_rippening As of_rip On of_qual.Offer_qual_id = of_rip.Offer_qual_num WHERE 1 = 1′ If @Family <> 0 SELECT @sql = @sql + ‘ AND (fam.Family_Id = @xFamily Or ([email protected] = 9 AND offe.Other_families IS NOT NULL))' If @Product <> 0 SELECT @sql = @sql + ‘ AND offe.Product_num = @xProduct' SELECT @sql = @sql + ‘ Order by offe.Offer_date Desc, offe.Offer_id Desc’ SELECT @param_list = ‘ @xCurrentPage int, @xPageSize int, @xFamily smallint, @xProduct smallint, @Offer_date datetime output, @Id nvarchar(1000) output' EXEC sp_executesql @sql, @param_list, @CurrentPage, @PageSize, @Family, @Product, @Offer_date output, @Id output SELECT @Offer_date, @Id Select @sql = ‘SET ROWCOUNT#%92 + convert(nvarchar(100), @PageSize) + â€˜ SELECT offe.Offer_id, offe.User_num, offe.Offer_date, offe.Offer_title From Offers As offe LEFT JOIN Products_name As prod On offe.Product_num = prod.Product_Id LEFT JOIN Families_product As fam On prod.Family_num = fam.Family_Id LEFT JOIN Offers_quality As of_qual On offe.Offer_id = of_qual.Offer_num LEFT JOIN Offers_rippening As of_rip On of_qual.Offer_qual_id = of_rip.Offer_qual_num WHERE 1 = 1 AND ((offe.Offer_date <#%92+ @Offer_date +#%92) Or (offe.Offer_date =#%92 + @Offer_date +#%92 And offe.Offer_id <=#%92 + @Id +#%92))' If @Family <> 0 SELECT @sql = @sql + ‘ AND (fam.Family_Id = @xFamily Or ([email protected] = 9 AND offe.Other_families IS NOT NULL))' If @Product <> 0 SELECT @sql = @sql + ‘ AND offe.Product_num = @xProduct' SELECT @sql = @sql + ‘ Order by offe.Offer_date Desc, offe.Offer_id Desc’ SELECT @sql = @sql + ‘ SET ROWCOUNT 0′ SELECT @param_list = ‘ @xCurrentPage int, @xPageSize int, @xFamily smallint, @xProduct smallint, @TotalRecords nvarchar(100) output' EXEC sp_executesql @sql, @param_list, @CurrentPage, @PageSize, @Family, @Product, @TotalRecords output SELECT @sql = ‘SELECT’ + @TotalRecords + ‘= COUNT(*) From Offers As offe LEFT JOIN Products_name As prod On offe.Product_num = prod.Product_Id LEFT JOIN Families_product As fam On prod.Family_num = fam.Family_Id LEFT JOIN Offers_quality As of_qual On offe.Offer_id = of_qual.Offer_num LEFT JOIN Offers_rippening As of_rip On of_qual.Offer_qual_id = of_rip.Offer_qual_num WHERE 1 = 1#%92 If @Family <> 0 SELECT @sql = @sql + ‘ AND (fam.Family_Id = @xFamily Or ([email protected] = 9 AND offe.Other_families IS NOT NULL))' If @Product <> 0 SELECT @sql = @sql + ‘ AND offe.Product_num = @xProduct' SELECT @param_list = ‘ @xCurrentPage int, @xPageSize int, @xFamily smallint, @xProduct smallint, @TotalRecords nvarchar(100) output ‘ EXEC sp_executesql @sql, @param_list, @CurrentPage, @PageSize, @Family, @Product, @TotalRecords output SELECT @TotalRecords SET NOCOUNT OFF END GO </pre id="code"> Somebody sees something wrong?

>>And now I receive next error What is the error? Madhivanan Failing to plan is Planning to fail

Well, indeed no error appears, simply none record is returned in any case

May take help of PROFILER in this case. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>