Problem in Dyanamic Sp Code - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Problem in Dyanamic Sp Code

Author

Topic

raky
Aged Yak Warrior

767 Posts

Posted - 2009-05-28 : 13:03:58

hi,

I have wrote a stored Procedure with dynamic sql. It ran fine and giving result set when input parameters are static.But it is not giving output when values are passed through Variables.... Please help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-28 : 13:09:12

sorry not fully clear. can you illustrate with sample data and code?

raky
Aged Yak Warrior

767 Posts

Posted - 2009-05-28 : 13:24:06

WHEN I PASSING INPUTS LIKE THIS

WHERE
CATEGORYID = 2
AND LANGUAGEID = 1

RECORDS ARE GETTING

WHEN I PASSING INPUTS LIKE THIS

WHERE
CATEGORYID = '+ CAST(@categoryID AS VARCHAR(50))+ '
AND LanguageID = '+ CAST(@LanguageID AS VARCHAR(50))+ '
RECORDS ARE NOT GETTING

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-28 : 13:34:54

and are you sure @categoryID & @LanguageID are holding correct values?

raky
Aged Yak Warrior

767 Posts

Posted - 2009-05-28 : 13:36:19

Yes

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-05-28 : 13:40:57

post your entire procedure

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-28 : 13:49:13

Replace EXEC with PRINT and see whether the SQL query formed is correct

a.rameshk
Starting Member

19 Posts

Posted - 2009-05-31 : 06:32:40

Hi,
I am giving some sample code here. I hope this can work

DECLARE @LanguageID INT
SET @LanguageID=1
DECLARE @CategoryID INT
SET @CategoryID=1
DECLARE @Param1 NVARCHAR(1000)
DECLARE @Param2 NVARCHAR(1000)
SET @Param2='@LanguageID INT'
SET @Param1='@CategoryID INT'
DECLARE @Stmet NVARCHAR(4000)
SET @Stmet=N'select * from Categories where CategoryID=@CategoryID AND LanguageID=@LanguageID'
PRINT @Stmet
EXEC sp_executesql @Stmet,@Param1,@CategoryID,@Param2,@LanguageID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-31 : 14:18:35

it would be better if you could post the results of PRINT sql string so that we can see what the problem is

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources