Dynamic Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamic Query

Author

Topic

binto
Yak Posting Veteran

59 Posts

Posted - 2010-04-16 : 07:09:18

Hi Team ,

DECLARE @QtyPerUnitMeasure DECIMAL(38,20)
DECLARE @QryQtyPerUntMsr VARCHAR(2000)

SET @QryQtyPerUntMsr='SELECT @QtyPerUnitMeasure=[Qty_ per Unit of Measure] FROM
['+@CompanyName+'$Item Unit of Measure] AS ItmUnitMeas
WHERE ItmUnitMeas.[Item No_]='''+@ItemNo+''' AND ItmUnitMeas.[Code]='''+@UnitMeasureCd+''''

Here 'Qty_ per Unit of Measure' and 'QryQtyPerUntMsr' are in decimal

ERROR
SELECT @QtyPerUnitMeasure=[Qty_ per Unit of Measure] FROM
[Jomsons Test$Item Unit of Measure] AS ItmUnitMeas
WHERE ItmUnitMeas.[Item No_]='BH1L' AND ItmUnitMeas.[Code]='BOXES'
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@QtyPerUnitMeasure".

plzz help me

Thanks & Regards
Binto Thomas

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-16 : 07:14:39

use sp_executesql instead of exec() and pass the variable in.

read this The Curse and Blessings of Dynamic SQL

KH
[spoiler]Time is always against us[/spoiler]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-16 : 07:16:13

Within the block of dynamic SQL, you cannot access local variables (including table variables) or parameters of the calling stored procedure. But you can pass parameters – in and out – to a block of dynamic SQL if you use sp_executesql.

http://www.sommarskog.se/dynamic_sql.html

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-16 : 07:16:50

This old rocker was sniped again...

No, you're never too old to Yak'n'Roll if you're too young to die.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2010-04-16 : 07:19:19

try this

DECLARE @QtyPerUnitMeasure DECIMAL(38,20)
DECLARE @QryQtyPerUntMsr NVARCHAR(2000)

SET @QryQtyPerUntMsr='SELECT @QtyPerUnitMeasureOUT=[Qty_ per Unit of Measure] FROM
['+@CompanyName+'$Item Unit of Measure] AS ItmUnitMeas
WHERE ItmUnitMeas.[Item No_]='''+@ItemNo+''' AND ItmUnitMeas.[Code]='''+@UnitMeasureCd+''''

EXEC SP_EXECUTESQL @QryQtyPerUntMsr, N'@QtyPerUnitMeasureOUT DECIMAL(38, 20) OUTPUT', 
			@QtyPerUnitMeasureOUT = @QtyPerUnitMeasure OUTPUT

SELECT @QtyPerUnitMeasure

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

binto
Yak Posting Veteran

59 Posts

Posted - 2010-04-22 : 00:39:44

quote:

Originally posted by PeterNeo

try this

DECLARE @QtyPerUnitMeasure DECIMAL(38,20)
DECLARE @QryQtyPerUntMsr NVARCHAR(2000)

SET @QryQtyPerUntMsr='SELECT @QtyPerUnitMeasureOUT=[Qty_ per Unit of Measure] FROM
['+@CompanyName+'$Item Unit of Measure] AS ItmUnitMeas
WHERE ItmUnitMeas.[Item No_]='''+@ItemNo+''' AND ItmUnitMeas.[Code]='''+@UnitMeasureCd+''''

EXEC SP_EXECUTESQL @QryQtyPerUntMsr, N'@QtyPerUnitMeasureOUT DECIMAL(38, 20) OUTPUT', 
			@QtyPerUnitMeasureOUT = @QtyPerUnitMeasure OUTPUT

SELECT @QtyPerUnitMeasure

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

Thanx a lot PeterNeo for your support.But I am getting result as
'Qty_ per Unit of Measure'.My procedure consists so many queries including the same.But I want a single result.How can I get this result in a variable.

Thanks & Regards
Binto Thomas

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources