May 12, 2011 at 1:46 pm
I am trying to run this procedure:
USE [SyteLine_HQ_App]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Rpt_WeirSPMPurchaseOrderCommitmentspTest 0, ' '
ALTER PROCEDURE
[dbo].[Rpt_WeirSPMPurchaseOrderCommitmentspTest]
(
@MatlType FlagNyType = 1,
@BuyerNameType = ' ',
@VendorVendNumType = ' ',
@DueDateBegDateType = NULL,
@DueDateEndDateType= NULL
)
AS
DECLARE @sStr nvarchar(4000)
SET @sStr = N'
SELECT po.vend_num,
vendaddr.name,
poitem.Po_num,
Po_line,poitem.item,
Poitem.description,
(qty_ordered-Qty_received) Open_Qty,
item_cost,
(qty_ordered-Qty_received)*item_cost as Extcost,
due_date,
promise_date,
CASE WHEN due_date< = GETDATE() THEN MONTH(GETDATE()) ELSE MONTH(due_date) END month,
CASE WHEN due_date <= GETDATE() THEN YEAR(GETDATE()) ELSE YEAR(due_date) END year,
prodline.prod_line, po.buyer
FROM poitem
LEFT OUTER JOIN po on poitem.po_num=po.po_num
LEFT OUTER JOIN vendaddr on po.vend_num = vendaddr.vend_num
LEFT OUTER JOIN item on poitem.item=item.item
LEFT OUTER JOIN prodline on item.product_code=prodline.product_code
WHERE poitem.stat = ''O''
AND qty_ordered-qty_received > 0
AND ((po.buyer = @Buyer AND @Buyer <> '' '') OR (@Buyer = '' '' AND po.buyer BETWEEN ''0'' AND ''ZZ''))
AND ((po.vend_num = @Vendor AND @Vendor <> '' '') OR (@Vendor = '' '' AND po.vend_num BETWEEN ''0'' AND ''ZZ''))
'
IF @MatlType = 1 BEGIN
SET @sStr = @sStr + 'AND item.matl_type = ''M'''
END
EXEC SP_EXECUTESQL @sStr
and get the following error:
Msg 137, Level 15, State 2, Line 25
Must declare the variable '@Buyer'.
I have already declared it at the top. What else is it needing?
I am hoping someone can see what I am doing wrong with what I have provided, but if I need to provide test data I will gladly do so.
Thank you.
Steve
May 12, 2011 at 1:55 pm
The variables in the dynamic sql run in a different scope than the procedure. But it looks like you are not concatenating your variables into the sql string. When you wrote this line
AND ((po.buyer = @Buyer AND @Buyer <> '' '') OR (@Buyer = '' '' AND po.buyer BETWEEN ''0'' AND ''ZZ''))
did you mean to write it like this
AND ((po.buyer = ' + @Buyer + ' AND ' + @Buyer + '<> '' '') OR (' + @Buyer + ' = '' '' AND po.buyer BETWEEN ''0'' AND ''ZZ''))
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
May 12, 2011 at 1:58 pm
your variable types are not valid for SQL.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
May 12, 2011 at 1:58 pm
You've got a few options here.
When you execute dynamic SQL, it runs in a separate context from your SP, so the fact that you declared your variables at the top of your SP doesn't affect the dynamic SQL at all. So you could
A: Use the ability of sp_executeSQL to pass in variable declarations to your dynamic sql statement (full explanation here)
B: Get rid of the dynamic SQL.
Something like this instead:
SELECT po.vend_num,
vendaddr.name,
poitem.Po_num,
Po_line,poitem.item,
Poitem.description,
(qty_ordered-Qty_received) Open_Qty,
item_cost,
(qty_ordered-Qty_received)*item_cost as Extcost,
due_date,
promise_date,
CASE WHEN due_date< = GETDATE() THEN MONTH(GETDATE()) ELSE MONTH(due_date) END month,
CASE WHEN due_date <= GETDATE() THEN YEAR(GETDATE()) ELSE YEAR(due_date) END year,
prodline.prod_line, po.buyer
FROM poitem
LEFT OUTER JOIN po on poitem.po_num=po.po_num
LEFT OUTER JOIN vendaddr on po.vend_num = vendaddr.vend_num
LEFT OUTER JOIN item on poitem.item=item.item
LEFT OUTER JOIN prodline on item.product_code=prodline.product_code
WHERE poitem.stat = 'O'
AND qty_ordered-qty_received > 0
AND ((po.buyer = @Buyer AND @Buyer <> ' ') OR (@Buyer = ' ' AND po.buyer BETWEEN '0' AND 'ZZ'))
AND ((po.vend_num = @Vendor AND @Vendor <> ' ') OR (@Vendor = ' ' AND po.vend_num BETWEEN '0' AND 'ZZ'))
AND (@MatlType = 0 OR item.matl_type = 'M')
C: Break up the dynamic SQL and use the variable values outside of the Strings. Something like this:
'AND ((po.buyer = ''' + @Buyer + ''' AND'...
May 12, 2011 at 3:04 pm
Todd,
I inherited this code, so I am not quite sure what they were trying to do. I have made these variables = NULL and that part works fine.
Dan,
Sorry I forgot to mention that these are user defined data types.
Garadin,
Thank you very much. I have learned a lot in just a few minutes. I have chosen option B but will make note of the other two. I have seen option C used successfully also. Thanks for the link for option A.
May 12, 2011 at 3:54 pm
I know all about the horrors of inherited code, so I know what you're going through. :w00t:
It seems like the author was using dynamic sql to conditionally append that last where clause to the query. In that case, Seth's solution to remove the dynamic-ness from the statement is dead on. Glad you got it working.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
May 12, 2011 at 4:13 pm
Garadin (5/12/2011)
B: Get rid of the dynamic SQL.Something like this instead:
SELECT po.vend_num,vendaddr.name,
poitem.Po_num,
Po_line,poitem.item,
Poitem.description,
(qty_ordered-Qty_received) Open_Qty,
item_cost,
(qty_ordered-Qty_received)*item_cost as Extcost,
due_date,
promise_date,
CASE WHEN due_date< = GETDATE() THEN MONTH(GETDATE()) ELSE MONTH(due_date) END month,
CASE WHEN due_date <= GETDATE() THEN YEAR(GETDATE()) ELSE YEAR(due_date) END year,
prodline.prod_line, po.buyer
FROM poitem
LEFT OUTER JOIN po on poitem.po_num=po.po_num
LEFT OUTER JOIN vendaddr on po.vend_num = vendaddr.vend_num
LEFT OUTER JOIN item on poitem.item=item.item
LEFT OUTER JOIN prodline on item.product_code=prodline.product_code
WHERE poitem.stat = 'O'
AND qty_ordered-qty_received > 0
AND ((po.buyer = @Buyer AND @Buyer <> ' ') OR (@Buyer = ' ' AND po.buyer BETWEEN '0' AND 'ZZ'))
AND ((po.vend_num = @Vendor AND @Vendor <> ' ') OR (@Vendor = ' ' AND po.vend_num BETWEEN '0' AND 'ZZ'))
AND (@MatlType = 0 OR item.matl_type = 'M')
Errr.....
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
The original code had the same problem.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 13, 2011 at 1:06 pm
GilaMonster (5/12/2011)
Garadin (5/12/2011)
Errr.....http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
The original code had the same problem.
Thanks for noting that Gail. I knew you needed to add a RECOMPILE but was incorrect in thinking that WITH RECOMPILE (which I've been using) worked the same way as the statement level option. I'll have to go fix that in a few places where I used it myself.
sdownen05: Beyond that, look at Gail's method of concatenation (for my option C) in that same article as hers is a much better method for doing it.
May 13, 2011 at 1:27 pm
Garadin (5/13/2011)
Thanks for noting that Gail. I knew you needed to add a RECOMPILE but was incorrect in thinking that WITH RECOMPILE (which I've been using) worked the same way as the statement level option. I'll have to go fix that in a few places where I used it myself.
Bear in mind that's only for SQL 2008 and 2008 R2, and only for some builds of SQL 2008.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 13, 2011 at 2:13 pm
GilaMonster (5/13/2011)
Garadin (5/13/2011)
Thanks for noting that Gail. I knew you needed to add a RECOMPILE but was incorrect in thinking that WITH RECOMPILE (which I've been using) worked the same way as the statement level option. I'll have to go fix that in a few places where I used it myself.Bear in mind that's only for SQL 2008 and 2008 R2, and only for some builds of SQL 2008.
Indeed, which is why I was using the procedure level and not the statement level in the first place (we're still using mostly 2005), so fixing it will probably entail re-evaluating it and using an injection proofed version of dynamic SQL.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply