Dynamic sql - 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

SQL Server Development (2000)

Dynamic sql

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-06-28 : 07:42:06

Wilfred writes "I am new to sql:
Basically i want to select a value into a variable but the table can change.

basically i want to know a way of accomplishing a query like this

select sqlstr='SELECT @ObjTbId = ID FROM '+@sysobj+' WHERE ID = object_id('+@objtbName+')'

print @ObjTbId

where by @sysobj table can change

I have appened the whole function bellow if u need it

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/**
* Object Name: fn_getTableIdByName
* Object Type: Pcikfords User Defined Function
* Programmer : wokoli
* ALTER Date: 18 -June -2004
* Description: Return id for a table object
* Revision History:
*
* Parameter @objtbName - table name
*/

ALTER function fn_getTableIdByName
(
@objtbName varchar(1000)
)
RETURNS int
AS

BEGIN
DECLARE @ObjTbId INT
DECLARE @sqlstr nvarchar(1000)
DECLARE @sysobj varchar(1000)

select @ObjTbId=-1
SELECT @sysobj=PARSENAME('pubs.dbo.syscolumns',3)+'.'+ PARSENAME('pubs.dbo.syscolumns',2)+'.syscolumns'

select sqlstr='SELECT @ObjTbId = ID FROM '+@sysobj+' WHERE ID = object_id('+@objtbName+')'

exec (@sqlstr)

IF @ObjTbId IS null
SELECT @ObjTbId = xusertype FROM systypes WHERE NAME = @objtbName

RETURN @ObjTbId
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Than you verry much in advance"

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-28 : 08:28:52

See
http://www.nigelrivett.net/sp_executeSQL.html

declare @i int
exec sp_executesql N'select @i = 999', N'@i int output', @i output
select @i

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources