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.
| Author |
Topic |
|
itmasterw
Yak Posting Veteran
90 Posts |
 Posted - 2013-04-04 : 11:13:58
|
| 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 codeIF Not EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].' + @MemUnST + ' ') AND name = N'idx_Mem_Rel_cd_Subscib_Nbr')BeginExec ('CREATE NONCLUSTERED INDEX [idx_Mem_Rel_cd_Subscib_Nbr] ON [dbo].[Members_UNET_' + @in_st + ']' + '([Mem_Rel_Cd] ASC,[Subscrib_Nbr] ASC)')EndITM |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-04-04 : 11:22:18
|
| 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 OptimizerTG |
 |
|
|
itmasterw
Yak Posting Veteran
90 Posts |
Posted - 2013-04-04 : 11:33:48
|
| 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 |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-04-04 : 14:06:05
|
| 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 OptimizerTG |
|
|
|
itmasterw
Yak Posting Veteran
90 Posts |
Posted - 2013-04-05 : 07:25:51
|
| 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 youITM |
|
|
|
|
|
|
Trying to insert a varible into a statement