sql server - Dynamic SQL / Parameters / Search -


i have searched on forum , found examples of how can send list of comma separated values sql , process them.

i wanted know if can create dynamic parameters , pass them stored procedure or better way of making particular task easier?

i take string , create array of 'space' separated values , each of values create new parameter (eg searchtext0, searchtext1, etc).

for each item in searchstring parameters.(new sqlparameter("@searchtext" + array.indexof(searchstring, item).tostring, item))) next

the problem occurs when running stored procedure if there many parameters created array sp not execute, little works fine can treat parameter empty value.

@susercode varchar (10) = '', @searchtext1 varchar (300) = '', @searchtext2 varchar (300) = '', @searchtext3 varchar (300) = '', @searchtext4 varchar (300) = ''  begin select top (20) prodtbl.material + ' | ' + prodtbl.description + ' [' + prodtbl.plant + ']' mat, prodtbl.material, prodtbl.description, prodtbl.plant  scodes inner join prodtbl on scodes.plant = prodtbl.plant (scodes.usercode = @susercode) , (prodtbl.bin <> 'dscntd') ,  (prodtbl.material '%' + @searchtext1 + '%' or prodtbl.description '%' + @searchtext1 + '%' or prodtbl.plant '%' + @searchtext1 + '%' or prodtbl.manuf '%' + @searchtext1 + '%') , (prodtbl.material '%' + @searchtext2 + '%' or prodtbl.description '%' + @searchtext2 + '%' or prodtbl.plant '%' + @searchtext2 + '%' or prodtbl.manuf '%' + @searchtext2 + '%') , (prodtbl.material '%' + @searchtext3 + '%' or prodtbl.description '%' + @searchtext3 + '%' or prodtbl.plant '%' + @searchtext3 + '%' or prodtbl.manuf '%' + @searchtext3 + '%') , (prodtbl.material '%' + @searchtext4 + '%' or prodtbl.description '%' + @searchtext4 + '%' or prodtbl.plant '%' + @searchtext4 + '%' or prodtbl.manuf '%' + @searchtext4 + '%') end

i know can write command string , run like able not worry having touch vbcode , make changes stored procedure.

there around 50 columns , want allow users search around 10 of them without making sp huge , without having add new row every potential parameter created in vbcode.

thanks in advance. kev


Comments

Post a Comment

Popular posts from this blog

IF statement in MySQL trigger -

this query code in mysql trigger. if 1=2 select 'yes'; else select 'no'; end if; but has following error: if '1'='2' select 'yes' error code: 1064. have error in sql syntax; check manual corresponds mysql server version right syntax use near 'if '1'='2' select 'yes'' @ line 1 0.000 sec what solution?
Read more

c++ - What does MSC in "// appease MSC" comments mean? -

i run across // appease msc in c++ comments, e.g. here : const int& array::operator[](int offset) const { int mysize = getitssize(); if (offset >= 0 && offset < getitssize()) return ptype[offset]; throw xboundary(); return ptype[offset]; // appease msc! } what msc stand ? the author means microsoft's compiler, more formally known msvc.
Read more

javascript - Blogger related post gadget image Resize s72-c [ Need Expert Help ] -

i run blogger blog , use follow code javascript code (requires no jquery) show related post labels/categories of post. <script type='text/javascript'> var defaultnoimage=&quot;http://1.bp.blogspot.com/-m72rpguntq0/vuokijudn_i/aaaaaaaaboi/lq18sceunsg/w72/favicon-tik.png&quot;; var maxresults=16; var splittercolor=&quot;#d4eaf2&quot;; var relatedpoststitle=&quot;related posts&quot;; </script> <script type='text/javascript'>//<![cdata[ var relatedtitles = new array(); var relatedtitlesnum = 0; var relatedurls = new array(); var thumburl = new array(); function related_results_labels_thumbs(json) { (var = 0; < json.feed.entry.length; i++) { var entry = json.feed.entry[i]; relatedtitles[relatedtitlesnum] = entry.title.$t; try { thumburl[relatedtitlesnum] = entry.media$thumbnail.url } catch (error) {...
Read more