sql server reporting services: Mulitple stored proc parameters -- sql server reporting services

You're in the

sql server reporting services

group:

Mulitple stored proc parameters

Eric
6/1/2005 1:42:04 PM

sql server reporting services: 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

Re: Mulitple stored proc parameters

Bruce L-C [MVP]
6/1/2005 4:04:22 PM

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

[quoted text, click to view]

Re: Mulitple stored proc parameters

Wayne Snyder
6/1/2005 5:51:17 PM

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

[quoted text, click to view]

Re: Mulitple stored proc parameters

Eric
6/2/2005 5:41:01 AM

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

[quoted text, click to view]

Re: Mulitple stored proc parameters

Bruce L-C [MVP]
6/3/2005 9:02:18 AM

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

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.

home · blog · groups

Questions? Comments? Contact the dn