CODEWITHSUNDEEP
sqlsql-server-2008
Aada
Aada

Reputation: 1640

In stored procedure, need to access the column value in the variable where Tablename is taken as a variable too

CREATE PROCEDURE dbo.usp_testing

AS
BEGIN
DECLARE @STATE INT,@TBLNAME sysname,@TrID VARCHAR(50),@FAMILYID VARCHAR(50),@SQL varchar(8000)

DECLARE cur CURSOR FOR
SELECT TrID,TableName 
FROM LogTable
WHERE Prefix='vid'
AND YEAR(DateTime)=YEAR(GETDATE())AND MONTH(DateTime)=MONTH(GETDATE())AND  DAY(DateTime)=DAY(GETDATE())

OPEN cur

FETCH NEXT FROM cur
INTO @TrID,@TBLNAME

IF(@TBLNAME='Student' OR @TBLNAME='Family' OR @TBLNAME='College')
BEGIN
**select @SQL='SELECT MemberID FROM'+' '+@TBLNAME+' '+'where TrId='+@TrID
select @FAMILYID=EXEC(@SQL)**

In this line, its gving me an error. 

select @SQL='select @ID=(SELECT MemberID FROM'+' '+@TBLNAME+' '+'where TrId='+@TrID +')'

as well as in this too

exec(@sql) 

END

END
GO

I need to put the MemberID in the variable @ID

Hi, I have done few changes as explained below

ALTER PROCEDURE [dbo].[USP_TESTSP]

AS
BEGIN
DECLARE @SQL nvarchar(max),@TABLENAME sysname,@id nvarchar(max)
    SELECT @TABLENAME='student'
    select @SQL= 'select @id=MemberID FROM'+' '+@TABLENAME+' '+'where TrId=11091'
    exec sp_executesql @SQL,'@id nvarchar(max)output',@id=@id output
    --select exec (@SQL)

END

but getting an error as:

Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.

Upvotes: 1

Views: 2029

Answers (3)

Andomar
Andomar

Reputation: 238226

Consider using sp_executesql with an output parameter:

declare @SQL nvarchar(max)
set @SQL='SELECT @FAMILYID = MemberID FROM ' + @TBLNAME + ' where TrId=' + @TrID
exec sp_executesql @SQL, N'@FAMILYID int out', @FAMILYID = @FAMILYID out

Upvotes: 1

Andriy M
Andriy M

Reputation: 77707

The parameter of sp_executesql where the formal arguments of your dynamic query are declared, '@id nvarchar(max)output', should be a unicode string. In Transact-SQL, to specify that a string constant is in fact a unicode string, you need to put N before the opening quotation mark, like this:

N'@id nvarchar(max)output'

So your USP_TESTSP should look like this:

ALTER PROCEDURE [dbo].[USP_TESTSP]

AS
BEGIN
DECLARE @SQL nvarchar(max),@TABLENAME sysname,@id nvarchar(max)
    SELECT @TABLENAME='student'
    select @SQL= 'select @id=MemberID FROM'+' '+@TABLENAME+' '+'where TrId=11091'
    exec sp_executesql @SQL,N'@id nvarchar(max)output',@id=@id output
    --select exec (@SQL)

END

Upvotes: 1

Robert
Robert

Reputation: 25763

It works for me:

select @SQL='SELECT @FAMILYID=MemberID FROM'+' '+@TBLNAME+' '+'where TrId='+@TrID
select EXEC(@SQL)

Upvotes: 0

Related Questions