ouput variables and dynamic sql

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

SQL Server Development (2000)

ouput variables and dynamic sql

Author Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-13 : 08:08:21

maulin writes "i need to do a lookup for the value of a single field in my database. i don't want the overhead of creating a recordset. so i thought i would use the command object and some dynamic sql with a stored procedure.

lets say i want to get the "Name" field for a given ID.

I basically tried this and failed. Any ideas?

VBSCRIPT:


Function GetIt()
Set objComm = Server.CreateObject("ADODB.Command")
		
objComm.ActiveConnection = someopenconnection
		
objComm.CommandText = "ps_GetForeignValue"
objComm.CommandType = adCmdStoredProc
		
objComm.Parameters.Append = objComm.CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue)

objComm.Parameters.Append = objComm.CreateParameter("tableName", adVarChar, adParamInput, 255, tableName)

objComm.Parameters.Append = objComm.CreateParameter("fieldName", adVarChar, adParamInput, 255, fieldName)

objComm.Parameters.Append = objComm.CreateParameter("ID", adVarChar, adParamInput, 255, ID)

objComm.Parameters.Append = objComm.CreateParameter("value", adVarChar, adParamOutput, 255)
		
objComm.Execute , , adExecuteNoRecords

GetIt = objComm.Parameters("value")
End Function

STORED PROCEDURE

Alter Procedure ps_GetForeignValue

	(
		@tableName varchar(255),
		@fieldName varchar(255),
		@ID varchar(255),
                @value varchar(255) OUTPUT
 
	)

As
	set nocount on
	DECLARE @SQL varchar(255)
	SET @SQL = 'SELECT @value=' + @fieldName + ' FROM ' + @tableName + ' WHERE ID=' + @ID

	EXECUTE(@SQL)


	return

----------

this returns "you must declare @value".

i know the easy workaround is just to accept the recordset created from the above and use that to answer my GetIt function, but it seems like the above should be able to work, even if the syntax isn't quite right.

thanks in advance"

ToddV
Posting Yak Master

218 Posts

Posted - 2002-02-13 : 09:06:10

Youu can use output parameters with sp_executesql to get this done for an example:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12602

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources