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

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic Query

Author

Topic

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2005-05-04 : 09:50:12

Hi guys

i need to build some code dynamic

SET QUOTED_IDENTIFIER off
declare @StrSql varchar(8000)
declare @db varchar(20)
declare @crBy varchar(3)
declare @crByNo varchar(20)
begin

set @db='digicustomer'
set @crBy='Develipdigi@yahoo.com'
set @StrSql = "select @crByNo=userno from " + @db + "..cad_user_master where userid='" + @crBy + "'"

select @StrSql
exec (@StrSql)
end

Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@crByNo'.

======================================
Ask to your self before u ask someone

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-04 : 09:54:32

Could you supply the DDL For

cad_user_master

Brett

8-)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-05-04 : 09:58:42

you'll need to use sp_executesql for this.

example in northwind


use northwind
declare @customerid varchar(10)
EXEC sp_executeSQL 	N'select @customerid = customerid from orders where orderid = 10500', 
		N'@customerid varchar(10) output', @customerid output
select @customerid

Go with the flow & have fun! Else fight the flow

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-04 : 10:32:35

True...we've been here before...

http://weblogs.sqlteam.com/brettk/archive/2005/01/27/4029.aspx

But I also had trouble with the your code...


DECLARE @StrSql varchar(8000), @db varchar(20), @crBy varchar(3), @crByNo varchar(20)

SELECT @db='digicustomer', @crBy='Develipdigi@yahoo.com'
SELECT @StrSql = 'select @crByNo=userno from ' + @db + '..cad_user_master where userid=''' + @crBy + ''''

select @StrSql

Brett

8-)

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2005-05-05 : 04:39:34

Thanks..

i tried

set @StrSql = "select @crByNo=userno from " + @db + "..cad_user_master where userid='" + @crBy + "'"
EXEC sp_executeSQL @StrSql ,N'@crByNo nchar(3) output, @crBy nvarchar(100)', @crByNo output

i get a error

Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 82
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

======================================
Ask to your self before u ask someone

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-05-05 : 04:55:13

you dont need the striked part.
number of arguments in the second and third part of the sp_ExecuteSql must be same.
do you understand how that sproc works?

set @StrSql = "select @crByNo=userno from " + @db + "..cad_user_master where userid='" + @crBy + "'"
EXEC sp_executeSQL @StrSql ,N'@crByNo nchar(3) output~~, @crBy nvarchar(100)~~', @crByNo output

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources