Mulitple stored proc parameters

Hi,
I have the following command text as my dataset :
declare @.SQL varchar(255)
select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
@.RoofSection
exec (@.SQL)
Both parameters are nvarchar(50) strings. However if I want my query to work
when I enter the parameter i need to put quotes around the @.RoofSection
parameters otherwise the query doesn't work.
What troubles me the most is that @.Facility doesn't need quotes :s
Any input on this?
ThxIf you are going to do this you need to plan on putting single quotes around
all text parameters (I noticed from query analyzer that sometimes it is OK
with this for the first parameter but it depends, for instance, if I do a %
then it wants it in single quotes).
Unless you are needing to dynamically switch databases then this is all you
have to do:
sp_rptRoofSection @.Facility , @.RoofSection
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:C225BCF0-BFAD-416E-956C-5A30B0D1A2AA@.microsoft.com...
> Hi,
> I have the following command text as my dataset :
> declare @.SQL varchar(255)
> select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
> @.RoofSection
> exec (@.SQL)
> Both parameters are nvarchar(50) strings. However if I want my query to
> work
> when I enter the parameter i need to put quotes around the @.RoofSection
> parameters otherwise the query doesn't work.
> What troubles me the most is that @.Facility doesn't need quotes :s
> Any input on this?
> Thx
>|||Generally speaking, if an SP character type parameter ( the actual parameter
value I mean) does NOT contain spaces or other special characters, it does
not have to be quoted. Quotes are required when the param value does contain
the special chars... So it is a good idea to always quote, then you do not
have to worry about it further.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:C225BCF0-BFAD-416E-956C-5A30B0D1A2AA@.microsoft.com...
> Hi,
> I have the following command text as my dataset :
> declare @.SQL varchar(255)
> select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
> @.RoofSection
> exec (@.SQL)
> Both parameters are nvarchar(50) strings. However if I want my query to
> work
> when I enter the parameter i need to put quotes around the @.RoofSection
> parameters otherwise the query doesn't work.
> What troubles me the most is that @.Facility doesn't need quotes :s
> Any input on this?
> Thx
>|||Yes I do indeed plan to dynamically change Database.
How can I put the quotes in my command string so the parameters are
automatically surrounded by quotes when they are passed to the stored proc?
My params do contain spaces and have a mix of numbers and chars into them.
Every single combination of quotes I enter makes an error.
Here is the command string again (the one that does work when I manually
enter my quotes into the values of the params):
declare @.SQL varchar(255)
select @.SQL = @.DBName + '.dbo.sp_rptRoofSection ' + @.Facility + ', '+
@.RoofSection
exec (@.SQL)
thx
"Bruce L-C [MVP]" wrote:
> If you are going to do this you need to plan on putting single quotes around
> all text parameters (I noticed from query analyzer that sometimes it is OK
> with this for the first parameter but it depends, for instance, if I do a %
> then it wants it in single quotes).
> Unless you are needing to dynamically switch databases then this is all you
> have to do:
> sp_rptRoofSection @.Facility , @.RoofSection
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:C225BCF0-BFAD-416E-956C-5A30B0D1A2AA@.microsoft.com...
> > Hi,
> >
> > I have the following command text as my dataset :
> >
> > declare @.SQL varchar(255)
> > select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
> > @.RoofSection
> > exec (@.SQL)
> >
> > Both parameters are nvarchar(50) strings. However if I want my query to
> > work
> > when I enter the parameter i need to put quotes around the @.RoofSection
> > parameters otherwise the query doesn't work.
> >
> > What troubles me the most is that @.Facility doesn't need quotes :s
> >
> > Any input on this?
> >
> > Thx
> >
> >
>
>|||Note that you do not have to use a script like this. I use an expression
because with an expression I can first assign it to a textbox so I can see
the result. Then when I have it correct I then use the expression as the
source (in generic query window).
= Parameters!DBName.Value & ".dbo.sp_rptRoofSection " & "'" &
Parameters!Facility.Value & "'"
Note it is double quote, single quote, double quote.
If you want to use the script then what you do is you put two single quotes
for every single quote you want. For instance:
select @.SQL = @.DBName + '.dbo.sp_rptRoofSection ''' + @.Facility + ''', '''+
@.RoofSection + ''''
So this '''' (four single quotes) ends up with 1 single quote. The outer two
are enclosing the string. In the modification above everything you see are
single quotes.
Again, I like using an expression because it makes it easier to test, plus
enclosing a string in double quotes and just putting a single quote where
you need it is easier to do.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:2F087327-F40A-4D85-BB07-2EC8C5F72815@.microsoft.com...
> Yes I do indeed plan to dynamically change Database.
> How can I put the quotes in my command string so the parameters are
> automatically surrounded by quotes when they are passed to the stored
> proc?
> My params do contain spaces and have a mix of numbers and chars into them.
> Every single combination of quotes I enter makes an error.
> Here is the command string again (the one that does work when I manually
> enter my quotes into the values of the params):
> declare @.SQL varchar(255)
> select @.SQL = @.DBName + '.dbo.sp_rptRoofSection ' + @.Facility + ', '+
> @.RoofSection
> exec (@.SQL)
> thx
> "Bruce L-C [MVP]" wrote:
>> If you are going to do this you need to plan on putting single quotes
>> around
>> all text parameters (I noticed from query analyzer that sometimes it is
>> OK
>> with this for the first parameter but it depends, for instance, if I do a
>> %
>> then it wants it in single quotes).
>> Unless you are needing to dynamically switch databases then this is all
>> you
>> have to do:
>> sp_rptRoofSection @.Facility , @.RoofSection
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Eric" <Eric@.discussions.microsoft.com> wrote in message
>> news:C225BCF0-BFAD-416E-956C-5A30B0D1A2AA@.microsoft.com...
>> > Hi,
>> >
>> > I have the following command text as my dataset :
>> >
>> > declare @.SQL varchar(255)
>> > select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
>> > @.RoofSection
>> > exec (@.SQL)
>> >
>> > Both parameters are nvarchar(50) strings. However if I want my query to
>> > work
>> > when I enter the parameter i need to put quotes around the @.RoofSection
>> > parameters otherwise the query doesn't work.
>> >
>> > What troubles me the most is that @.Facility doesn't need quotes :s
>> >
>> > Any input on this?
>> >
>> > Thx
>> >
>> >
>>sql