|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
stored proc with multiple parameterscreate proc spselect_cust
@custno varchar (16) as select * from us_cust where custno in( @custno) Hi I am trying to creat a stored proc that can accept about three customer numbers but the one above only does one customer number does anyone know if this is possible thanks Sammy
http://www.aspfaq.com/2248
Show quote "Sammy" <Sa***@discussions.microsoft.com> wrote in message
news:7BC63E7D-C3DE-405F-8FA7-2358EFD6FF96@microsoft.com... > create proc spselect_cust > @custno varchar (16) > as > > select * from us_cust > where custno in( @custno) > > > Hi I am trying to creat a stored proc that can accept about three customer > numbers but the one above only does one customer number does anyone know > if > this is possible > > thanks > > Sammy
http://www.sommarskog.se/arrays-in-sql.html
Show quote "Sammy" <Sa***@discussions.microsoft.com> wrote in message
news:7BC63E7D-C3DE-405F-8FA7-2358EFD6FF96@microsoft.com... > create proc spselect_cust > @custno varchar (16) > as > > select * from us_cust > where custno in( @custno) > > > Hi I am trying to creat a stored proc that can accept about three customer > numbers but the one above only does one customer number does anyone know > if > this is possible > > thanks > > Sammy You need to use dynamic SQL (there's no array processing
in SQL Server) Also, don't prefix your procs with sp or sp_ Are the customer numbers varchars or ints? create proc dbo.usp_select_cust @custno1 varchar (16), @custno2 varchar (16), @custno3 varchar (16) as set nocount on declare @sql nvarchar(4000) set @sql = 'select * from us_cust where custno in (' + @custno1 + ',' + @custno2 + ',' + @custno3 + ')' exec sp_executesql @stmt = @sql go Show quote "Sammy" <Sa***@discussions.microsoft.com> wrote in message news:7BC63E7D-C3DE-405F-8FA7-2358EFD6FF96@microsoft.com... > create proc spselect_cust > @custno varchar (16) > as > > select * from us_cust > where custno in( @custno) > > > Hi I am trying to creat a stored proc that can accept about three customer > numbers but the one above only does one customer number does anyone know if > this is possible > > thanks > > Sammy "Armando Prato" <apr***@REMOVEMEkronos.com> wrote in message No he doesn't, and it's best for him to avoid that solution.news:eGMmwJ9gFHA.2372@TK2MSFTNGP14.phx.gbl... > You need to use dynamic SQL (there's no array processing > in SQL Server) Read the links Aaron and I posted if you want to know why and for other solutions. I use the list-of-integers and list-of-strings method for most of my "array" parameters. True, the dynamic SQL method looks like the easiest and fastest way to do this, but like most things in life, the easiest way is rarely the best way. I respect your position on the subject and I have read these articles
in the past. In my opinion, I think there are cases where there's coding overkill in trying to persuade people to not use dynamic SQL in cases where they just need a simple, code-light solution. Highlighting other solutions are certainly beneficial. If he/she suspects there could be potential performance issues, he/she could certainly examine the iterative method (or any of the others outlined). Thanks Show quote "Raymond D'Anjou" <rdanjou@savantsoftNOSPAM.net> wrote in message news:umROqM%23gFHA.2572@TK2MSFTNGP10.phx.gbl... > "Armando Prato" <apr***@REMOVEMEkronos.com> wrote in message > news:eGMmwJ9gFHA.2372@TK2MSFTNGP14.phx.gbl... > > You need to use dynamic SQL (there's no array processing > > in SQL Server) > > No he doesn't, and it's best for him to avoid that solution. > Read the links Aaron and I posted if you want to know why and for other > solutions. > I use the list-of-integers and list-of-strings method for most of my "array" > parameters. > True, the dynamic SQL method looks like the easiest and fastest way to do > this, but like most things in life, the easiest way is rarely the best way. > > If Erland can't convince you, I won't even try.
For the poster's problem though, dynamic SQL is not the best solution. My opinion... Yes. But I think that most of the experienced posters here will agree. Note : I don't consider myself to be in the group of the experienced posters yet. ;-) Show quote "Armando Prato" <apr***@REMOVEMEkronos.com> wrote in message news:eC6aVt%23gFHA.2916@TK2MSFTNGP14.phx.gbl... >I respect your position on the subject and I have read these articles > in the past. In my opinion, I think there are cases where there's coding > overkill in trying to persuade people to not use dynamic SQL in cases > where they just need a simple, code-light solution. Highlighting other > solutions are certainly beneficial. If he/she suspects there could be > potential > performance issues, he/she could certainly examine the iterative > method (or any of the others outlined). > > Thanks > > "Raymond D'Anjou" <rdanjou@savantsoftNOSPAM.net> wrote in message > news:umROqM%23gFHA.2572@TK2MSFTNGP10.phx.gbl... >> "Armando Prato" <apr***@REMOVEMEkronos.com> wrote in message >> news:eGMmwJ9gFHA.2372@TK2MSFTNGP14.phx.gbl... >> > You need to use dynamic SQL (there's no array processing >> > in SQL Server) >> >> No he doesn't, and it's best for him to avoid that solution. >> Read the links Aaron and I posted if you want to know why and for other >> solutions. >> I use the list-of-integers and list-of-strings method for most of my > "array" >> parameters. >> True, the dynamic SQL method looks like the easiest and fastest way to do >> this, but like most things in life, the easiest way is rarely the best > way. >> >> > > Again, I can see where there are times where dynamic SQL is not a good
solution. I'm just not so sure if the originally posted problem is that complicated where a dynamic SQL solution wouldn't suffice. It's just my opinion and it's worked well for me in other situations with no ill effects. Not a lot of problems posted revolve around high volume, multi-gig databases. Certainly, I wouldn't look to dynamic SQL if that were the case. Perhaps the company he/she works for is very successful and has 300,000 customer rows. However, what if the table only holds 1000 customers? or 500? or 100? Rhetorically speaking, is it worth the extra work? The original poster can make that call. Again, it's always a good thing to offer alternatives but sometimes I think that some solutions are the equivalent of killing an ant with a sledgehammer. Just my opinion. Show quote "Raymond D'Anjou" <rdanjou@savantsoftNOSPAM.net> wrote in message news:%23bdqFC$gFHA.3616@TK2MSFTNGP12.phx.gbl... > If Erland can't convince you, I won't even try. > For the poster's problem though, dynamic SQL is not the best solution. > My opinion... Yes. But I think that most of the experienced posters here > will agree. > Note : I don't consider myself to be in the group of the experienced posters > yet. ;-) > > "Armando Prato" <apr***@REMOVEMEkronos.com> wrote in message > news:eC6aVt%23gFHA.2916@TK2MSFTNGP14.phx.gbl... > >I respect your position on the subject and I have read these articles > > in the past. In my opinion, I think there are cases where there's coding > > overkill in trying to persuade people to not use dynamic SQL in cases > > where they just need a simple, code-light solution. Highlighting other > > solutions are certainly beneficial. If he/she suspects there could be > > potential > > performance issues, he/she could certainly examine the iterative > > method (or any of the others outlined). > > > > Thanks > > > > "Raymond D'Anjou" <rdanjou@savantsoftNOSPAM.net> wrote in message > > news:umROqM%23gFHA.2572@TK2MSFTNGP10.phx.gbl... > >> "Armando Prato" <apr***@REMOVEMEkronos.com> wrote in message > >> news:eGMmwJ9gFHA.2372@TK2MSFTNGP14.phx.gbl... > >> > You need to use dynamic SQL (there's no array processing > >> > in SQL Server) > >> > >> No he doesn't, and it's best for him to avoid that solution. > >> Read the links Aaron and I posted if you want to know why and for other > >> solutions. > >> I use the list-of-integers and list-of-strings method for most of my > > "array" > >> parameters. > >> True, the dynamic SQL method looks like the easiest and fastest way to do > >> this, but like most things in life, the easiest way is rarely the best > > way. > >> > >> > > > > > >  |
|||||||||||||||||||||||
Other interesting topics