SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Trying to insert a varible into a statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

itmasterw
Yak Posting Veteran

90 Posts

Posted - 04/04/2013 :  11:13:58  Show Profile  Reply with Quote
Hi,
I am trying to put a varible(@MemUnST) in the following statement and I am not sure if this can be done thee way I have it below does not work. The execute part wprks but the IF Not EXISTS part is not, and I know it is because of the varible.
If it can be done can someone tell me how please?
Thank you

My code
IF Not EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].' + @MemUnST + ' ') AND name =

N'idx_Mem_Rel_cd_Subscib_Nbr')
Begin
Exec ('CREATE NONCLUSTERED INDEX [idx_Mem_Rel_cd_Subscib_Nbr] ON [dbo].[Members_UNET_' + @in_st + ']' + '([Mem_Rel_Cd]

ASC,[Subscrib_Nbr] ASC)')
End

ITM

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 04/04/2013 :  11:22:18  Show Profile  Reply with Quote
I don't see a problem with that code assuming you have declared both variables as some flavor of char datatype and assigned a value to them. (@MemUnST, @in_st)

why isn't it working? error? what is the error?

Be One with the Optimizer
TG
Go to Top of Page

itmasterw
Yak Posting Veteran

90 Posts

Posted - 04/04/2013 :  11:33:48  Show Profile  Reply with Quote
Here I want it to create an index if it does nto exist. However, when I test it, with the index on there, it goes to the next line and trys to create it; and naturly comes back with:
The operation failed because an index or statistics with name 'idx_Mem_Rel_cd_Subscib_Nbr' already exists on table 'dbo.Members_UBET_CA'.

ITM
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 04/04/2013 :  14:06:05  Show Profile  Reply with Quote
print out whatever the concatenated strings resolve to when the variables are populated. Then it will probably be obvious to you what the problem is. Do that for both the IF EXISTS condition as well as the CREATE INDEX statement.

Be One with the Optimizer
TG
Go to Top of Page

itmasterw
Yak Posting Veteran

90 Posts

Posted - 04/05/2013 :  07:25:51  Show Profile  Reply with Quote
Thank you
I found the problem this OBJECT_ID(N'[dbo].' + @MemUnST + ' ') had to be like this OBJECT_ID(@MemUnST) with the dbo put in the variable.
Thank you

ITM
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000