September 24, 2003 at 2:01 pm
Hi all,
As you know, the following statement assigns QUESTID to @myID.
1. select @myID = QUESTID from QUESTIONS where NAME = 'SQL Sever'
If both table name and condition are dynamic, I use this statement
2. exec ('select QUESTID from ' + @QUESTIONS +
' where NAME = ' + '''' + @myName + '''')
But how can I assign this QUESTID to @myID?
Thx for your help.
September 24, 2003 at 3:09 pm
sp_executesql supports parameters:
DECLARE @MyId int, @Questions sysname, @myName varchar(20), @sql nvarchar(300)
SELECT @Questions = 'Questions', @myName = 'Sql Server'
SET @sql = N'SELECT @TheId = QuestID FROM ' + @Questions +
N' WHERE Name = ''' + @myName + ''''
EXEC sp_executesql @sql, N'@TheId int OUTPUT', @TheId = @MyId OUTPUT
SELECT @MyId
--Jonathan
--Jonathan
September 29, 2003 at 9:52 am
Hi Jonathan,
You are right. I am trying to the sp_executesql way. However, I still have a problem when I run the following script.
declare @sqlString nvarchar(100)
declare @paremeter nvarchar(100)
declare @intVar int
declare @LastName varchar(20)
set @sqlString = N'select @Lname = max(lname) from pubs.dbo.employee where job_lvl = @level'
set @paremeter = N'@level int, @LastName varchar(20) OUTPUT'
set @intVar = 35
execute sp_executesql @sqlString, @paremeter, @level = @intVar, @Lname = @LastName
select @LastName
The script seems good though. The error message I got is:
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@Lname'.
Do you have any ideas about it?
Thx a lot.
September 29, 2003 at 10:05 am
quote:
Hi Jonathan,You are right. I am trying to the sp_executesql way. However, I still have a problem when I run the following script.
declare @sqlString nvarchar(100)
declare @paremeter nvarchar(100)
declare @intVar int
declare @LastName varchar(20)
set @sqlString = N'select @Lname = max(lname) from pubs.dbo.employee where job_lvl = @level'
set @paremeter = N'@level int, @LastName varchar(20) OUTPUT'
set @intVar = 35
execute sp_executesql @sqlString, @paremeter, @level = @intVar, @Lname = @LastName
select @LastName
The script seems good though. The error message I got is:
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@Lname'.
Do you have any ideas about it?
Thx a lot.
declare @sqlString nvarchar(100)
declare @parameter nvarchar(100)
declare @intVar int
declare @LastName varchar(20)
set @sqlString = N'select @Lname = max(lname) from pubs.dbo.employee where job_lvl = @level'
set @parameter = N'@level int, @Lname varchar(20) OUTPUT'
set @intVar = 35
execute sp_executesql @sqlString, @parameter, @level = @intVar, @Lname = @LastName OUTPUT
select @LastName
--Jonathan
--Jonathan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply