Query problem - 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)

Query problem

Author

Topic

rubs_65
Posting Yak Master

144 Posts

Posted - 2004-05-21 : 14:02:39

Hi,

Following is the code that is not working in my stored procedure...
I am checking the existence of table and if it exists assign value 'Y'
to variable @already but when I run this procedure it always assign null to @already..How to fix this code?

set @cmd = 'IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(''t_acc_usage_' + @suffix + ''')) set ' + @already + '=''Y'''
exec sp_executesql @cmd

Also i tried the following code:
set @cmd = 'IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(''t_acc_usage_' + @suffix + ''')) set @already1 = ''N'''
exec sp_executesql @cmd,N'@already1 varchar(1) output',@already output

This code always assign 'N' to @already irrespective of table exists or not

Thanks
--Rubs

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-05-21 : 14:25:39

Why are you using Dynamic SQL to perform the check?

declare @already char(1)
set @already = 'N'

if exists 
(
	select table_name 
	from information_schema.tables 
	where table_name like 't_acc_usage_' + @suffix 
)
	set @already = 'Y'

select @already

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-21 : 14:33:36

DECLARE
@cmd NVARCHAR(4000),
@suffix NVARCHAR(55)

SELECT @suffix = 'HUH?'

set @cmd = 'DECLARE @already NCHAR(1) IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(''t_acc_usage_' + @suffix + ''')) BEGIN SET @already = ''Y'' END ELSE BEGIN SET @already = ''N'' END SELECT @already'
exec sp_executesql @cmd

PRINT @cmd

This is what's wrong with your syntax. Like ehorn said though, you shouldn't use dynamic sql for this at all.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources