SQL Server - Table name as variable in query -

-

first, know has been addressed before - i've done research , you'll see below, have tried multiple versions work.

i trying set query in variable can set , query uses variable. here's code:

-- set category want check declare @validationcategory varchar = 'dept'  declare @validationtable varchar = (select validationtable masterfiles..categories category = @validationcategory , tabletovalidate = 'xref') declare @validationfield varchar = (select validationfield masterfiles..categories category = @validationcategory , tabletovalidate = 'xref')  exec(' select distinct category masterfiles.dbo.xref category = ''' + @validationcategory + '''     , (new_value not in (select ''' + @validationfield + '''                             ' + @validationtable + ')     , old_value not ''%new%''     or (new_value null or new_value = ''''))     )' )

when run getting error:

msg 102, level 15, state 1, line 6
incorrect syntax near ')'.

the i've been able glean error level 15 means error correctable user. great! if find out syntax problem is...

i'm suspecting may due fact @validationtable table, not string, i'm not sure how address that.

after more research tried put query in variable , run exec on variable:

--... declare @query nvarchar set @query = ' select distinct category masterfiles.dbo.xref category = ''' + @validationcategory + '''     , (new_value not in (select ''' + @validationfield + '''                             ' + @validationtable + ')     , old_value not ''%new%''     or (new_value null or new_value = ''''))     )' exec(@query)

that ran without errors, didn't output - ran successfully.

after more research getting output of query executed variable, came (from example found here on so):

--... declare @vquery nvarchar set @vquery = ' select distinct category masterfiles.dbo.xref category = ''' + @validationcategory + '''     , (new_value not in (select ''' + @validationfield + '''                             ' + @validationtable + ')     , old_value not ''%new%''     or (new_value null or new_value = ''''))     )'  declare @vi int --declare @vquery varchar(1000)  exec sp_executesql     @query = @vquery     , @params = '@vi int output'     , @vi = @vi output  select @vi

that resulted in error:

msg 214, level 16, state 3, procedure sp_executesql, line 1
procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.

so what's correct way go this. in research on i've seen examples use stored procedure. i'd able without stored procedure because query used copy/pasting query query window in client's ssms, setting table variable , running query. moving on next client same thing.

i need able run on servers installed either sql server 2008 or 2012.

this first attempt generating.. 

select distinct category    masterfiles.dbo.xref   category = 'd'         , (new_value not in (select 'm' m)             , old_value not '%new%'             or (new_value null or new_value = '')         )     ) --

this has ) , doesn't make sense

on top of of this.. you're declaring variables varchar no length equals 1 character

declare @validationcategory varchar = 'dept'  declare @validationtable varchar = (select validationtable masterfiles..categories category = @validationcategory , tabletovalidate = 'xref') declare @validationfield varchar = (select validationfield masterfiles..categories category = @validationcategory , tabletovalidate = 'xref')

@validationcategory equal 'd' in case.. make sure add appropriate lengths varchars


Comments

Post a Comment

Popular posts from this blog

ruby - Trying to change last to "x"s to 23 -

-
i have been playing around irb , have string looks this: irb(main):072:0> puts ["[\"4354 5432 5432 xxxxx\", \"6547 6547 8543 xxxxx\", \"2344 6543 6674 xxxxx\", \"2346 6236 7543 xxxxx\", \"1273 5585 5587 xxxxx\"]"] => nil irb(main):073:0> what want gsub last 2 "x" s of each 17 digit combination, 23 . what i've tried far (i've been using rubular): a.gsub(/\d[x]/,"23") <= grabs "x" s i'm close a.gsub(/\w[x{2}]/,"23") <= grabs "x" s grabs two digits each combination. is there easier way i'm not understanding? a.gsub(/\d{4} \d{4} \d{4} xxx\kxx/, '23') \d{4} = 4 digits what \k drop matched far. can use don't replace entire number, still able validate have in desired format.
Read more

jquery - Clone last and append item to closest class -

-
this bare-bones code on page: <div class="list-container"> <div class="single-list-item"> <button>remove</button> </div> <div class="single-list-item"> <button>remove</button> </div> <div class="single-list-item"> <button>remove</button> </div> </div> <p><button class="add-list-item">add</button></p> <div class="list-container"> <div class="single-list-item"> <button>remove</button> </div> <div class="single-list-item"> <button>remove</button> </div> <div class="single-list-item"> <button>remove</button> </div> </div> <p><button class="add-list-item">add</button></p> <di...
Read more

css - Can I use the :after pseudo-element on an input field? -

-
i trying use :after css pseudo-element on input field, not work. if use span , works ok. <style type="text/css"> .mystyle:after {content:url(smiley.gif);} .mystyle {color:red;} </style> this works (puts smiley after "buu!" , before "some more") <span class="mystyle">buuu!</span>a more this not work - colors somevalue in red, there no smiley. <input class="mystyle" type="text" value="somevalue"> what doing wrong? should use pseudo-selector? note: cannot add span around input , because being generated third-party control. :after , :before not supported in internet explorer 7 , under, on elements. it's not meant used on replaced elements such form elements (inputs) , image elements . in other words it's impossible pure css. however if using jquery can use $(".mystyle").after("add smiley here"); api docs on .after to ...
Read more