May 15, 2005 at 6:51 pm
CREATE PROCEDURE GetCurrentStudentName_New
@stuTable varchar(30),@permNum varchar(50),@StuName varchar(200) OUTPUT
AS
DECLARE @Query nvarchar(1000)
SET @Query =’SELECT @StuName=(LASTNAME+'’,’'+ FIRSTNAME) FROM '+
@stuTable+' WHERE RIGHT(PERMNUM, 7) = ‘’'+ @ permNum+’’’’
PRINT @Query
EXEC sp_executesql @Query
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I need to assign the selected value from the query to the output variable..But It is not able to recognize the OUTPUT parameter @stuName inside the @Query variable.
I can not write this as static query also in this case, as table name is also passed as a parameter to the SP...
I think, the alternative I have is to create a temporary table ...
Is there any other better approach to resolve this...!!!!!!!!!!!!
Thanks,
Raj
May 15, 2005 at 8:00 pm
Hi,
I have used the following approach for now,
ALTER PROCEDURE GetCurrentStudentName_New
@stuTable varchar(30),@permNum varchar(7),@stuName varchar(80) OUTPUT
AS
DECLARE @Query varchar(1000)
CREATE TABLE #TMP(stuName varchar(80))
SET @Query='SELECT (LASTNAME+'',''+FIRSTNAME) as stuName FROM '
+ @stuTable + ' WHERE RIGHT(PERMNUM, 7) ='''+ @permNum+''''
INSERT INTO #TMP
EXEC(@Query)
SELECT @stuName=stuName FROM #tmp
GO
Pls do suggest me, if there is any better approach to handle this..
Thanks,
Raj
May 15, 2005 at 8:20 pm
The best thing to do is to review your database design, so that you know what the table is called!
You could even use a view if you have a really good reason (not sure there could be one) to store the information.
Rob
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au
May 15, 2005 at 11:49 pm
In this case u can use outparm of sp_executesql .
For more than one result a cursor can be used as output param
I think this may solve u r problem
DECLARE @SQLCURSORSTRING nvarchar(500)
DECLARE @Varcur cursor
DECLARE @PARAMDEF nvarchar(200)
DECLARE @name varchar(200)
DECLARE @stuTable varchar(200)
SET @SQLCURSORSTRING = N'SET @Varcur = CURSOR FOR SELECT LASTNAME +
FIRSTNAME FROM Test1 ; OPEN @Varcur'
SET @PARAMDEF = N' @Varcur CURSOR OUTPUT'
EXEC sp_executesql @SQLCURSORSTRING,@PARAMDEF,@Varcur=@Varcur output
FETCH NEXT FROM @Varcur INTO @name
WHILE (@@fetch_status <> -1)
Begin
print @name
FETCH NEXT FROM @Varcur INTO @name
end
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply