Dyanmic SQL Results - 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 2005 Forums

Transact-SQL (2005)

Dyanmic SQL Results

Author

Topic

jonathans
Starting Member

40 Posts

Posted - 2008-02-21 : 02:51:28

Hi guys,

Got a little bit of a problem, that i cant get my head around, even though the answer is probably simple. I am trying to select data from a dynamic sql statement (the tableName is passed in as a variable) and if a row exists then else code.

DECLARE @EmployeesId INT
DECLARE @TableName VARCHAR(100)
SET @TableName = 'MyWorkTable'
SET @EmployeesId = 1

-- Check if Table has Data with WHERE Condition
IF EXISTS(EXECUTE('SELECT [Id] FROM [' + @TableName + '] WHERE [CapturerId] = ' + CAST(@EmployeesId AS VARCHAR(10))))
BEGIN
END

This is what im trying to do.

Any idea's as to how to do this?
Thanks

WARNING: Running on cold coffee!

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-02-21 : 03:38:30

Try this

DECLARE @EmployeesId INT
DECLARE @TableName VARCHAR(100)
SET @TableName = 'tblproviders'
SET @EmployeesId = 1

DECLARE @SQL VARCHAR(MAX),
@cnt INT
SELECT @SQL = 'SELECT ProviderId FROM [' + @TableName + '] WHERE ProviderId = ' + CAST(@EmployeesId AS VARCHAR(10))

-- Check if Table has Data with WHERE Condition
EXECUTE(@SQL)
SELECT @cnt = @@ROWCOUNT

IF (@cnt <> 0)
BEGIN
SELECT 1
END

jonathans
Starting Member

40 Posts

Posted - 2008-02-21 : 03:46:07

Hey peter thanks for the quick reply, thought it would be something easy like that.

Next question though, how would fix this:
[i]Msg 443, Level 16, State 14, Procedure CanEditCustomerSubsidiary, Line 32
Invalid use of side-effecting or time-dependent operator in 'EXECUTE STRING' within a function.[/i]

WARNING: Running on cold coffee!

jonathans
Starting Member

40 Posts

Posted - 2008-02-21 : 03:47:29

Never mind, i cant call execute inside a function, so going to move that bit of code to a SP and execute the SP from the function.

Thanks for the help.

WARNING: Running on cold coffee!

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-02-21 : 03:48:59

you can't use dynmaic sql in a function

check the link
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60857

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources