Passing NULL to DataSet parmameter

Hey All,

I have a number Stored Procs that have been around for a while that pull the entire list, or if I pass an ID, will just the record for that ID like below.

I want to be able to use these querries to poplate Multi-Select parameter dropdowns. going to the Data tab and creating a new dataset, I put in the SP name, and close the window. I then go to the Red ! point to preview the data it prompts me for my ID parmaeter on my SP. In the dropdown list it has '<Null>' (no ' though). When I run it, it works fine and returns all of my records.

When I run the report, it errors saying I didn't pass a parm for ID. I go back to the data tab, and edit my DataSet hitting the elipse. I go to the 3 tab called parameters, and type the following I get the following errors:

@.ID = <Null> - ERROR - [BC30201] Expression expected

@.ID= - ERROR - Return statement in function must return a value

@.ID = Null --ERROR - Null constant not supported use System.DBNull instead

@.ID = System.DBNull -ERROR - [BCS30691] DBNull is a type in System and cannot be used in an expression

@.ID=System.DBNull.Value NO ERROR, but it does not return anything either. I also did a SQL Trace, and I can see that it doesn't even send the querry to the database.

Does anyone know another magic value I can pass to get this to work?

I am being a little stuborn, I know that I could just create new procs, and wrap up the null, but the more stuff you create the more you have to maintain, so I would prefer to reuse these.

Thanks in advance.

Eric Wild

PS: My company is moving from crystal reports to Reporting service, and Reporting services is Rocks! It is very intuitve, simple and straign forward. The formatting is easy with the table and the matrix control blows away the crosstab control in crystal. Also, I'm finding that because crystal was so un extendable, that I would spend hours shaping sql to get over it's blemishes, and hours shaping it in the report, only to sometimes reliaze that the proposed onetime hack wouldn't work, and have to start all over! So far with RSS any tips and tricks I have learned can very easily be applied to any report I work on! Aslo, I do mostly interanet web apps, and it is nice to dump my reports on the Report Server, and not worry about haing to create a web page, create a datasource and all the ansilary stuff to go along with it. The only thing I don't like is the name 'Roporting Services': It does not stick out too far in Google Searches like 'AJAX.NET' or 'ASP.NET'. Anyway kudoes to the Reporting Services team!

ALTER PROC [dbo].[spGetLaborRole]
@.ID INT = NULL
AS
BEGIN
SELECT ID, Descr
FROM dbo.LaborRole
WHERE ( (ID = @.ID) OR (@.ID IS NULL) )

Hello Eric,

Can you verify that in your report parameter definition (Report menu --> Report Parameters), the 'Allow null values' checkbox is selected for your ID parameter?

Jarret

|||

Jarret,

That worked!

I guess I didn't see them as being related. I think of report parameters as things that communicate with the ouside world, and not related to my internl querrires. I wouldn't want a prompt to the end user showing ID: NULL to run the report. I can see though there is a hidden check so it not for end users. Cool thanks!

Here is steps on how to fix this.

1) go to the data tab and select the elipse. select the parameters tab and delete the @.ID=... stuff I put in and close Window.

2) Go to the Layout tab, and from the menu select Report/Report Parameters...

3) a new Parameter is in the list to the left callled ID.

-Check Allow Nulls

- Check Hidden

-Verify Default Value NULL is bubbled in below.

Thanks again

Eic Wild

Passing non-URL Friendly Parameters?

I have a parameter in my SQL report which is a system account number. This
account number often contains non-URL friendly characters (i.e. '<', '>',
';', ' ' , etc...). How can I create some type of substitition function to
work around this issue?
I reall need to pass the parameter through the URL. Thank you in advance.
- MaxWhat instead of using the GET method, you use the POST method to send your
parameters
Med Bouchenafa
"Max Tyack" <MaxTyack@.discussions.microsoft.com> a écrit dans le message de
news: D3AC72E4-77AE-4410-91E3-DC2DAB367842@.microsoft.com...
>I have a parameter in my SQL report which is a system account number. This
> account number often contains non-URL friendly characters (i.e. '<', '>',
> ';', ' ' , etc...). How can I create some type of substitition function to
> work around this issue?
> I reall need to pass the parameter through the URL. Thank you in advance.
> - Max|||Do you need the link on a static page or from an other report?
Reporting Services should recognize escaped uri parameters. So instead of
'http://Hello World' you can use 'http://Hello%20World'. The same applies to
<, > and so on. The % values are the hex-ascii values, so you might need to
look up an ascii table and do a string replace ( like strUri.Replace(" ",
"%20") )
In .NET 2.0 you should look up for the Uri class and use EscapeUriString or
EscapeDataString.
I hope it's understandable, else you can try it at
http://www.greymana.net/examples/escape.html . For Non-URI friendly
parameters the Dest (param) might be more intersting. It's all in client-side
javascript.
"Max Tyack" wrote:
> I have a parameter in my SQL report which is a system account number. This
> account number often contains non-URL friendly characters (i.e. '<', '>',
> ';', ' ' , etc...). How can I create some type of substitition function to
> work around this issue?
> I reall need to pass the parameter through the URL. Thank you in advance.
> - Max

Passing Multi-Value parameters to a Data Driven Subscription

Hi,

I've been asked to set up a data driven subscription for a number of reports which use multi value parameters. For example, show me all transactions against the following departments: IT, Building Services, Accounts.

As an interactive report it's simple, the user just selects the relevant departments, but as a data driven subscription I can't seem to find the correct format to pass the selections through.

Has anyone tried this before?

Thanks,

Dan

please search on "Multi-Value parameters to a Data Driven Subscription" in this forum.

Passing Multivalue Parameters Through a Report Link

I've created a line chart in my report which lists number of transactions by month. I've also created a report to list transactions by day. My goal is for the user to select a month and link the report to the graph with the selected month divided up into days (in other words, the user wants to see the number of transactions for each day in the selected month).

Anyway, everything is working perfectly except for one thing. In the list of parameters that I am passing to the report for each day, I want to pass a multivalue parameter which contains all the transaction IDs the user selected in creating the report (these are supplied by a multivalue parameter). However, in the parameter dialog box where it asks for a value to send to the awaiting parameter, I do not know how to supply more than one value. If I need to pass one value, it will work fine. However, I would like to do something like:

JOIN(Parameters!TransactionID.value, ",")

for the value of TransactionID, but when I generate the report it is not accepting the values. I'm pretty sure its just a format issue, and I just need to know how exactly I should pass these values.

I already understand that when passing multivalue parameters in a URL you need something like:

TransactionID=1&TransactionID=2

...in order to select multiple values. However, this is a slightly different situation. I'm really running out of ideas, so any help would be much appreciated.

Thank you guys so much

can you please paste your select statement, multivalue parameters are automatically rendered if you have something like this in your select

select * from employees where managerid IN(select managerid from managers)

|||

Have you tried:

SELECT *

FROM Managers

WHERE ManagerID in (@.ManagerParm)

This works in Oracle (except for : instead of @.) and I would be surprised if it did not work in SQL server.

NOTE you will not be able to test it using the !, but it it will work for the report.

|||

I'm sorry I wasn't clear. The project I have is in RS 2005 and uses an analysis services data source and the select statement is entirely in MDX. However, the problem really isn't in the select statement. My problem is that I am creating a report link and I want to pass all the values selected in a multivalue parameter to the other report.

If you want to see reproduce the problem, try these steps:

Create a report with a multivalue parameter. Add a textbox. Right-click and select properties. Click the Navigation tab and select "Jump to Report". Select a valid report that accepts a multivalue parameter. Click the parameters button. In the parameter name column, select the multivalue parameter to receive the values. In the parameter value column, I need to pass the values for the current multivalue parameter. This is the problem I am having.

|||

Please read this related thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=163803&SiteID=1

It describes the various scenarios of passing multi value parameters to a drillthrough or subreport. In your case, it should be sufficient to just specify =Parameters!ParameterName.Value to pass all values along.

However, note that if the target report contains a parameterized MDX query and you want to use the passed-in parameter values directly in that query, the parameter values in the main report must represent the UniqueNames (not the caption which is usually the parameter label) - otherwise the target MDX query will most likely not work.

-- Robert

|||Thank you, it works perfectly!!sql

Passing mdx parameters to RS through URL (SharePoint Integrated Mode)

Hi there,

I'm running MOSS 2007 in Integrated Mode, and I have a number of reports that are deployed to the Reports Library, and are working just fine when you go there and run them interactively.

Now however, I need to address the report via a URL, and pass it a parameter. Just to add a bit more pain, the report is in mdx, and expects an mdx parameter.

Here is the parameter "prm_cost_centre":

[DIM LEARNER AIM].[COST CENTRE DESC].&[Business]

Here is the URL of the report:

http://vmmoss:88/ReportsLibrary/Report1.rdl

(If you hit that URL as is, it runs fine, using the default parameter)

Now, I know that we have to escape the ampersand, so I'm thinking that this URL should do it.

http://vmmoss:88/ReportsLibrary/Report1.rdl&rs:Command=Render&prm_cost_centre=[DIM LEARNER AIM].[COST CENTRE DESC].%26[Business]

But it doesn't - It throws a 400 Bad Request error.

I know I must be very close, but I just can't work out what URL I need to pass in order to get the damn thing to run.

Any ideas?

I don't have very much experience with MDX params, but I might recommend escaping the periods or possibly the [brackets] too. Sorry I can't offer much more advice than that, but as in any debug scenario, try truncating the param string until you get something that works, then go from there!

|||

It's not possible - you can't pass parameters through the URL when running Reporting Services in Integrated Mode.

http://technet.microsoft.com/en-us/library/bb326290.aspx

How lame is that? All of a sudden, Integrated Mode becomes...worthless to most people, surely.

Passing mdx parameters to RS through URL (SharePoint Integrated Mode)

Hi there,

I'm running MOSS 2007 in Integrated Mode, and I have a number of reports that are deployed to the Reports Library, and are working just fine when you go there and run them interactively.

Now however, I need to address the report via a URL, and pass it a parameter. Just to add a bit more pain, the report is in mdx, and expects an mdx parameter.

Here is the parameter "prm_cost_centre":

[DIM LEARNER AIM].[COST CENTRE DESC].&[Business]

Here is the URL of the report:

http://vmmoss:88/ReportsLibrary/Report1.rdl

(If you hit that URL as is, it runs fine, using the default parameter)

Now, I know that we have to escape the ampersand, so I'm thinking that this URL should do it.

http://vmmoss:88/ReportsLibrary/Report1.rdl&rs:Command=Render&prm_cost_centre=[DIM LEARNER AIM].[COST CENTRE DESC].%26[Business]

But it doesn't - It throws a 400 Bad Request error.

I know I must be very close, but I just can't work out what URL I need to pass in order to get the damn thing to run.

Any ideas?

I don't have very much experience with MDX params, but I might recommend escaping the periods or possibly the [brackets] too. Sorry I can't offer much more advice than that, but as in any debug scenario, try truncating the param string until you get something that works, then go from there!

|||

It's not possible - you can't pass parameters through the URL when running Reporting Services in Integrated Mode.

http://technet.microsoft.com/en-us/library/bb326290.aspx

How lame is that? All of a sudden, Integrated Mode becomes...worthless to most people, surely.

sql

Passing in variable number of parameters to a stored procedure

I am fairly new to MSSQL. Looking for a answer to a simple question.

I have a application which passes in lot of stuff from the UI into a stored procedure that has to be inserted into a MSSQL 2005 database. All the information that is passed will be spilt into 4 inserts hitting 4 seperate tables. All 4 inserts will be part of a stored procedure that have to be in one TRANSACTION. All but one insert are straight forward.

The structure of this table is something like

PKID
customerID
email address
....

customerID is not unique and can have n email addresses passed in. Each entry into this table when inserted into, will be passed n addresses (The number of email addresses passed is controlled by the user. It can be from 1..n). Constructing dynamic SQL is not an option. The SP to insert all the data is already in place. Typically I would just create the SP with IN parameters that I will use to insert into tables. In this case I can't do that since the number of email addresses passed is dynamic. My question is what's the best way to design this SP, where n email addresses are passed and each of them will have to be passed into a seperate insert statement? I can think of two ways to this...

Is there a way to create a variable length array as a IN parameter to capture the n email addresses coming in and use them to construct multiple insert statements?

Is it possible to get all the n email addresses as a comma seperated string? I know this is possible, but I am not sure how to parse this string and capture the n email addresses into variables before I construct them into insert statements.

Any other ways to do this? ThanksFrom a relational perspective, the best answer is to write a single stored procedure that takes one email address and processes it, and then call that procedure N times from your UI. This is because a relational database is based on relational algebra, and while that processes sets well as output, it doesn't process them nearly as easily as input.

If you decide to pursue your original idea and use a delimited (probably comma separated) list, you can use fSplit (http://www.dbforums.com/t997070.html) which I posted here ages ago. This is cleaner from the UI perspective, but it will eventually byte you because of the poor fit with relational databases.

-PatP|||This is because a relational database is based on relational algebra, and while that processes sets well as output, it doesn't process them nearly as easily as input.Really? I never knew that.

What's the basic reasoning around that Pat?|||Ok - I had a few mins. Played around.

CREATE TABLE InsertTable
(
MyPKFld VarChar(5) PRIMARY KEY
)

DECLARE @.i AS SmallInt

SET NOCOUNT ON

DECLARE @.Insert AS VarChar(2000)

--SELECT @.Insert = '10001,10022,20099,15073,28948,18737,90273,27910,3 7891'
SELECT @.Insert = '10001,10022,15073,18737,20099,27910,28948,37891,9 0273'

SELECT @.i = 1

DECLARE @.LoopUpper AS TinyInt

SELECT @.LoopUpper = (SELECT COUNT(*) FROM dbo.Split(@.Insert, ','))

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

DECLARE @.Start AS DateTime

SELECT @.Start = GETDATE()

WHILE @.i <= @.LoopUpper BEGIN

INSERT INTO InsertTable
SELECT Data
FROM dbo.Split(@.Insert, ',')
WHERE ID = @.i
SELECT @.i = @.i + 1

END

PRINT 'LOOP takes ' + CAST(DATEDIFF(ms, @.Start, GETDATE()) AS VarChar(4)) + 'ms'

DELETE
FROM InsertTable
WHERE MyPKFld > 10000

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

SELECT @.Start = GETDATE()

INSERT INTO InsertTable
SELECT Data
FROM dbo.Split(@.Insert, ',')
PRINT 'SET takes ' + CAST(DATEDIFF(ms, @.Start, GETDATE()) AS VarChar(4)) + 'ms'

DROP TABLE InsertTable
Basically tests looping and inserting one record on each pass and inserting a set. I typically get the set at between 1/3 and 2/3 the time the loop takes. What have I missed?|||What's the basic reasoning around that Pat?In a set based environment (where sets are fully supported by both the language itself and the implementation), there's no issue. SQL as a language doesn't support passing sets in (at least it doesn't yet, the new draft standard has basic support for them).

What you are doing is passing a complex argument (more than one atomic element in a single argument). That is outside of relational algebra altogether since it violates first normal form. The reason it runs faster is that you're trading multiple calls in a relational solution for processing complexity in a code oriented solution. It certainly works, and at least for now it is faster, but eventually it will get to the point that it causes problems.

-PatP|||Thanks Pat
I don't think I totally get you. The csv string is not normalised. However the function (or whatever code one might run) normalises the input. As far as SQL Server is concerned, it might always have been a set.

It certainly works, and at least for now it is faster, but eventually it will get to the point that it causes problems.By this do you mean it will be a bugger to maintain or do you mean there will be some sort of technical problem over time? If the latter - what would that be?

Grateful for the education as ever :)|||When you create a non-normalized interface like this, you've broken one of the fundamental building block "contracts" between a client and server. That relationship is either relational, or it isn't relational, and any non-relational interface makes the relationship between client and server non-relational.

This kind of change can make sense when you're implementing a different paradyme such as OOP. When you do that, you leave the relational world behind, so the rigorous "proofs" of behavior no longer have any meaning, but that happens any time you switch from one paradyme to another.

There are lots of really fundamental characteristics involved in relational processing. These make it predictable, which in turn makes it dependable. While relational technology certainly isn't the best possible way to do things, it is the best that I've found so far that is widely commercially supported and clearly understood by many professionals.

There are thousands of ways this can go wrong (and I've personally tried several hundred of those ;)). One example would be that you could have an application start out as a single server implementation, grow to use a cluster, expand further to use a cloud of replicating servers... When something goes worng :o in the process of getting data from a web server client into the data cloud, you have to start using network monitors to find the problem since you can't rely on which app/web server will initiate the conversation and which database server will process it. If you have bundled multiple calls into one and then rely on the server to parse them, you can no longer predict what the data "payload" will be exactly, so you need to start doing moderately sophisticated pattern matching. The process gets ugly, really fast.

Not everyone will face this specific problem. Given sufficient time though, I'll guarantee that you'll hit some problem related to the bundling effect. If you are making a paradyme shift, and that shift buys you something substantial in terms of coding time, support, ability to use new features, etc. then it is certainly worth considering. If all it buys you is a slight performance gain in exchange for the predictability of the pure relational model, I'd be hard pressed to "green light" this change.

-PatP|||Thanks Pat - appreciated :D

As it happens - I asked this question some time ago and got one yay and one nay from two of your esteemed peers hence why I jumped on your answer.
EDIT - plus my initial reading of your answer went against everything I thought I knew about SQL.sql

Passing different number of parameters to a Stored Procedure

Hi to all,

How can I Pass different number of parameters to a Stored Procedure?

In my Requirement,

Some times i want to pass 2 parameters only,

In some cases i want to pass 6 parameters.

How can i do this?

Please give me a solution.

Thanx in advance...

You will have to use optional parameters then. The procedure head should look like something as the following:

CREATE PROCEDURE SomeProc
(
SomeParam INT = NULL,
SomeOtherParameter INT = 2
)(...)

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de

Passing datetime parameter to stored procedure

I want to pass a date parameter to stored procedure which is expecting
smalldatetime. I need to subtract a number of days from the current time and
pass it to the stored procedure. I tried DateAdd("d", -1, Now()). I can
display the the result in a text field on the report but when passing it to
the stored procedure to retrieve data I get and error that it is in bad
format.
Thanks,
AntoninOn May 22, 11:03 pm, "Antonin" <Antonin.Koude...@.fmc.sa.gov.au> wrote:
> I want to pass a date parameter to stored procedure which is expecting
> smalldatetime. I need to subtract a number of days from the current time and
> pass it to the stored procedure. I tried DateAdd("d", -1, Now()). I can
> display the the result in a text field on the report but when passing it to
> the stored procedure to retrieve data I get and error that it is in bad
> format.
> Thanks,
> Antonin
You could do one of a few different things:
- Use the CDate() function to pass it to a stored procedure.
- Redefine the smalldatetime input parameter in the stored procedure
as datetime and manipulate it to be a smalldatetime somewhere else in
the stored procedure
- Try to use Datepart to split the report field and then concatenate
it with '/' and then convert it to a datetime (the stored procedure
might implicitly accept it as a smalldatetime, though I don't recall).
- A combination of the above.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks Enrique. Would you know the solution for my other problem?
When I try to deploy a report I get this error:
The underlying connection was closed: Could not establish trust
relationship for the SSL/TLS secure channel.
The same error I get when I try to open
http://localhost/Reports/Pages/Folder.aspx
Antonin
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1179923004.037985.311190@.q69g2000hsb.googlegroups.com...
> On May 22, 11:03 pm, "Antonin" <Antonin.Koude...@.fmc.sa.gov.au> wrote:
>> I want to pass a date parameter to stored procedure which is expecting
>> smalldatetime. I need to subtract a number of days from the current time
>> and
>> pass it to the stored procedure. I tried DateAdd("d", -1, Now()). I can
>> display the the result in a text field on the report but when passing it
>> to
>> the stored procedure to retrieve data I get and error that it is in bad
>> format.
>> Thanks,
>> Antonin
> You could do one of a few different things:
> - Use the CDate() function to pass it to a stored procedure.
> - Redefine the smalldatetime input parameter in the stored procedure
> as datetime and manipulate it to be a smalldatetime somewhere else in
> the stored procedure
> - Try to use Datepart to split the report field and then concatenate
> it with '/' and then convert it to a datetime (the stored procedure
> might implicitly accept it as a smalldatetime, though I don't recall).
> - A combination of the above.
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>sql

Passing an indeterminate number of parameters

Hi all,
Does anyone know if it's possible to somehow pass an unknown number of
parameters to a storerd procedure.
For example via an array of some description? Alternatively, in C# you can
use the keyword "params" when the number of parameters to be passed is
unknown at design time. I don't think there is any equivalent in SQL server
though is there?
Thanks everyone
Simon
Array datatype is not supported in SQL Server. However you can use certain
workarounds for such requirements, some of which are detailed at:
http://www.sommarskog.se/arrays-in-sql.html
Anith
|||Simon
As far as I know there is not a way to do what you ask. What I feel you will have to do is program your proc for the most parameters possible and give them default values. That way if you only pass a few then your proc knows what to do with the remainin
g values. You can easily build the string you pass to the proc in your code.
Hope this helps
Jeff Duncan
MCDBA, MCSE+I
|||Thanks guys. Both very useful answers
Kindest regards
Simon

Passing an indeterminate number of parameters

Hi all,
Does anyone know if it's possible to somehow pass an unknown number of
parameters to a storerd procedure.
For example via an array of some description? Alternatively, in C# you can
use the keyword "params" when the number of parameters to be passed is
unknown at design time. I don't think there is any equivalent in SQL server
though is there?
Thanks everyone
SimonArray datatype is not supported in SQL Server. However you can use certain
workarounds for such requirements, some of which are detailed at:
http://www.sommarskog.se/arrays-in-sql.html
Anith|||Simon
As far as I know there is not a way to do what you ask. What I feel you wil
l have to do is program your proc for the most parameters possible and give
them default values. That way if you only pass a few then your proc knows w
hat to do with the remainin
g values. You can easily build the string you pass to the proc in your code
.
Hope this helps
Jeff Duncan
MCDBA, MCSE+I|||Thanks guys. Both very useful answers
Kindest regards
Simon

Passing an indeterminate number of parameters

Hi all,
Does anyone know if it's possible to somehow pass an unknown number of
parameters to a storerd procedure.
For example via an array of some description? Alternatively, in C# you can
use the keyword "params" when the number of parameters to be passed is
unknown at design time. I don't think there is any equivalent in SQL server
though is there?
Thanks everyone
SimonArray datatype is not supported in SQL Server. However you can use certain
workarounds for such requirements, some of which are detailed at:
http://www.sommarskog.se/arrays-in-sql.html
--
Anith|||Simo
As far as I know there is not a way to do what you ask. What I feel you will have to do is program your proc for the most parameters possible and give them default values. That way if you only pass a few then your proc knows what to do with the remaining values. You can easily build the string you pass to the proc in your code
Hope this help
Jeff Dunca
MCDBA, MCSE+I|||Thanks guys. Both very useful answers
Kindest regards
Simon

Passing a report parameter from a Visual C# form to a report parameter

Request is to have a Requirement number from the requirement form generate a report in Reporting Services with the requirement number as a filter.

I can set up the parameter - how does the value get there? Should I be asking this question in the Visual C# group?

Thanks!

Terry B

I hope this this article will help.

Passing a parameter from a query in a link to another report

Hi,
I am trying to link to reports together, I want to be able to pass a ID
number returned from a query in this link. THe link currently looks
like this:
http://offsite/ReportServer?/LondonResults/Detailed Report by
LEA&LEA=(Fields!IDNumber.Value)&rs:Parameters=true
But in my parameter box in the second report the value I am getting is
(Fields!IDNumber.Value), when really I wan to see an ID number.
ANy ideas?
StephenI'm not exactly sure where you are entering it but if it is an expression it
should be something like:
="http://offsite/ReportServer?/LondonResults/Detailed Report by LEA&LEA="
&(Fields!IDNumber.Value) & "&rs:Parameters=true"
Neil
"stephen.adams@.forvus.co.uk" wrote:
> Hi,
> I am trying to link to reports together, I want to be able to pass a ID
> number returned from a query in this link. THe link currently looks
> like this:
> http://offsite/ReportServer?/LondonResults/Detailed Report by
> LEA&LEA=(Fields!IDNumber.Value)&rs:Parameters=true
> But in my parameter box in the second report the value I am getting is
> (Fields!IDNumber.Value), when really I wan to see an ID number.
> ANy ideas?
> Stephen
>

Passing a Database name as a parameter

I'm currently testing a system that replicates data across a number of different databases. Once testing has been completed I use a stored procedure to reset the data on the master database so that additional tests can be run. I want to write a stored procedure that will reset the data on slave databases the testing has just been run on, but rather than have a stored procedure on each slave just have one on the master, as I could be testing across 1-n slaves.

I can access slave data from the master datbase with [slave1].[dbo].[target-table], and am looking at passing the slave name as a parameter to the stored procedure so the command would be [@.SlaveName].[dbo].[target-table], but any text inside the [ ] seems to be taken as literal string

Any one have any pointers?

If the count or names of your slave databases is/are likely to change then you'd have to do this using dynamic SQL. You could load a cursor with the contents of 'SELECT [name] FROM master.sys.databases WHERE <--insert criteria-->' then iterate through the cursor and create and execute a SQL string for each value that's returned.

For information only, there's an undocumented stored proc called sp_MSForEachDB that can be used to execute a SQL script in each database. However, be warned that undocumented stored procs could be dropped or significantly changed between SQL Server releases, or even service packs, so should not be used in production code.

Chris

|||

Solution I cam up was in the stored procedure to have a

EXEC ('USE '+@.SlaveName +'<action to take>')

Passing a comma delimited string of parameters to a stored proc

Hello,

I have a number of multi-select parameters which I would like to send to a stored procedure within the dataset for use in the stored procedure's IN() statement which in turn is used to filter on or out particular rowsets.

I considered using a hidden string parameter set = " ' " + join(parameter.value, ',') + " ' " so that the hidden parameter would then contain a comma delimiated string of the values selected, which would then be sent on to the stored proc and used in the WHERE clause of one of the queries internal to the stored proc.

But before I start dedicating time to do this I wanted to inquire if anyone here with far more expertise could think of a faster or less system heavy method of creating a single string of comma delimited parameter selections?

Thanks.

I would recommend Dr. Lisa to you.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1705421&SiteID=1

|||

Hi,

You could create a function that would convert your string into a table and then use this table in you stored procedure.

Here is an example of a function that would convert comma separated list into a table:

http://blogs.vandamme.com/development/2007/06/parse_comma_sep.html

Then you could modify your SQL statement in the stored procedure tu use this function, something like this should do:

SELECT <Fields>

FROM <Table> a

JOIN <Function> (@.CommaSeparatedList) b ON b.uid = a.id

HTH,

|||

I usually use MVP Jens Suesmeyer's SPLIT function; it can be found here:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17

Pass variable number of parameters to a stored proc

Is it possible to create a stored proc that allows the passing of a variable
number of parameters? I'm creating a stored proc to populate a Suggested PO
form. It may require filtering on one or more vendor IDs (string data). I've
looked through BOL but nothing is jumping out at me. I also was looking at
the string manipulation functions for parsing a single variable but it looks
like it would be ugly. TIA!Hi,
You will have to use dynamic SQL inside the procedure to parse the parameter
variable which hold one or more input values.
See the below URL for the various usage of dynamic SQL.
http://www.sommarskog.se/dynamic_sql.html
Thanks
Hari
SQL Server MVP
"Ron Hinds" < __ron__dontspamme@.wedontlikespam_garagei
q.com> wrote in message
news:%23RizK7jxGHA.480@.TK2MSFTNGP06.phx.gbl...
> Is it possible to create a stored proc that allows the passing of a
> variable
> number of parameters? I'm creating a stored proc to populate a Suggested
> PO
> form. It may require filtering on one or more vendor IDs (string data).
> I've
> looked through BOL but nothing is jumping out at me. I also was looking at
> the string manipulation functions for parsing a single variable but it
> looks
> like it would be ugly. TIA!
>
>|||Ron Hinds wrote:
> Is it possible to create a stored proc that allows the passing of a variab
le
> number of parameters? I'm creating a stored proc to populate a Suggested P
O
> form. It may require filtering on one or more vendor IDs (string data). I'
ve
> looked through BOL but nothing is jumping out at me. I also was looking at
> the string manipulation functions for parsing a single variable but it loo
ks
> like it would be ugly. TIA!
>
>
Here is one approach:
CREATE PROCEDURE MyProc
@.Var1 INT = NULL,
@.Var2 INT = NULL
AS
SELECT Field1, Field2
FROM Table
WHERE
((@.Var1 IS NULL) OR (Field3 = @.Var1))
AND
((@.Var2 IS NULL) OR (Field4 = @.Var2))
The optimizer is smart enough to realize that if the left side of the OR
is true, there is no need to evaluate the right side.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Pass variable number of parameters to a stored proc

Is it possible to create a stored proc that allows the passing of a variable
number of parameters? I'm creating a stored proc to populate a Suggested PO
form. It may require filtering on one or more vendor IDs (string data). I've
looked through BOL but nothing is jumping out at me. I also was looking at
the string manipulation functions for parsing a single variable but it looks
like it would be ugly. TIA!Hi,
You will have to use dynamic SQL inside the procedure to parse the parameter
variable which hold one or more input values.
See the below URL for the various usage of dynamic SQL.
http://www.sommarskog.se/dynamic_sql.html
Thanks
Hari
SQL Server MVP
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:%23RizK7jxGHA.480@.TK2MSFTNGP06.phx.gbl...
> Is it possible to create a stored proc that allows the passing of a
> variable
> number of parameters? I'm creating a stored proc to populate a Suggested
> PO
> form. It may require filtering on one or more vendor IDs (string data).
> I've
> looked through BOL but nothing is jumping out at me. I also was looking at
> the string manipulation functions for parsing a single variable but it
> looks
> like it would be ugly. TIA!
>
>|||Ron Hinds wrote:
> Is it possible to create a stored proc that allows the passing of a variable
> number of parameters? I'm creating a stored proc to populate a Suggested PO
> form. It may require filtering on one or more vendor IDs (string data). I've
> looked through BOL but nothing is jumping out at me. I also was looking at
> the string manipulation functions for parsing a single variable but it looks
> like it would be ugly. TIA!
>
>
Here is one approach:
CREATE PROCEDURE MyProc
@.Var1 INT = NULL,
@.Var2 INT = NULL
AS
SELECT Field1, Field2
FROM Table
WHERE
((@.Var1 IS NULL) OR (Field3 = @.Var1))
AND
((@.Var2 IS NULL) OR (Field4 = @.Var2))
The optimizer is smart enough to realize that if the left side of the OR
is true, there is no need to evaluate the right side.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Pass record to user-defined function and create xml

Does anyone know if there is a way to pass a record from a select to a
user defined function in SQL Server 2000? The number and names of the
columns in the record will vary depending on the upload temp table
selected from... You should be able to see what I am trying to
accomplish bellow... If you have another idea, I'm open to
suggestions.
Example:
Declare @.CurrentFiscalYear smallint
Set @.CurrentFiscalYear = 2005
Create table ClaimEditLog(
TransactionId int,
XMLData varchar(7500),
EditDesc varchar(200),
LastUpdateId varchar(20),
LastUpdate datetime
)
Create Table #tbClaimUploadData(
TransactionId int IDENTITY (1, 1) NOT NULL ,
FiscalYear smallint,
AmountTypeId int,
Amount money
)
Insert #tbClaimUploadData
Select 2005, 2, 556.98
Insert #tbClaimUploadData
Select 2006, 2, 56.90
Insert into ClaimEditLog
Select TransactionId,
dbo.UDF_ConvertRecordToXML(*),
'The Fiscal Year is incorrect.',
'jporscha',
GetDate()
>From #tbClaimUploadData
Where FiscalYear <> @.CurrentFiscalYear
-- UDF_ConvertRecordToXML - Convert record to XML
Select * from ClaimEditLog
--Output
2,
'<XMLData><Record><TransactionId>2<TransactionId><FiscalYear>2006</FiscalYea
r><AmountTypeId>2</AmountTypeId><Amount>56.90</Amount></Record></XMLData>',
'The Fiscal Year is incorrect.',
2006-05-31 06:41:32.527You could store the complete record and use a computed column to give a XML
representation of the output...
So your table definition would be this...
Create table ClaimEditLog(
TransactionId int,
FiscalYear smallint,
AmountTypeId int,
Amount money,
XMLData AS
'<XMLData><Record><TransactionId>'
+ CAST( TransactionId as varchar(20) )
+ '<FiscalYear>' + CAST( FiscalYear AS char(4) ) + '
etc...',
EditDesc varchar(200),
LastUpdateId varchar(20),
LastUpdate datetime
)
Your insert would be...
insert claimeditlog (
TransactionId,
FiscalYear,
AmountTypeId,
Amount,
EditDesc,
LastUpdateId,
LastUpdate )
select TransactionId,
FiscalYear,
AmountTypeId,
Amount,
'The Fiscal Year is incorrect.',
'jporscha',
GetDate()
from #tbClaimUploadData
Select * from ClaimEditLog
And you'd get the XMLData output as text XML.
Make sense?
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
<porsch55@.yahoo.com> wrote in message
news:1149077706.631013.220560@.y43g2000cwc.googlegroups.com...
> Does anyone know if there is a way to pass a record from a select to a
> user defined function in SQL Server 2000? The number and names of the
> columns in the record will vary depending on the upload temp table
> selected from... You should be able to see what I am trying to
> accomplish bellow... If you have another idea, I'm open to
> suggestions.
> Example:
> Declare @.CurrentFiscalYear smallint
> Set @.CurrentFiscalYear = 2005
> Create table ClaimEditLog(
> TransactionId int,
> XMLData varchar(7500),
> EditDesc varchar(200),
> LastUpdateId varchar(20),
> LastUpdate datetime
> )
> Create Table #tbClaimUploadData(
> TransactionId int IDENTITY (1, 1) NOT NULL ,
> FiscalYear smallint,
> AmountTypeId int,
> Amount money
> )
> Insert #tbClaimUploadData
> Select 2005, 2, 556.98
> Insert #tbClaimUploadData
> Select 2006, 2, 56.90
> Insert into ClaimEditLog
> Select TransactionId,
> dbo.UDF_ConvertRecordToXML(*),
> 'The Fiscal Year is incorrect.',
> 'jporscha',
> GetDate()
> Where FiscalYear <> @.CurrentFiscalYear
> -- UDF_ConvertRecordToXML - Convert record to XML
> Select * from ClaimEditLog
> --Output
> 2,
> '<XMLData><Record><TransactionId>2<TransactionId><FiscalYear>2006</FiscalY
ear><AmountTypeId>2</AmountTypeId><Amount>56.90</Amount></Record></XMLData>'
,
> 'The Fiscal Year is incorrect.',
> 2006-05-31 06:41:32.527
>

Pass parameters to complex procedure.

Hello, I have this Stored procedure, a very complex one, and it works fine with the parameters wrote on it. But when I try to change the number to variables @.param1, I got errors, can somebody tell me how to replace this please.

DECLARE @.return_value int,
@.lvaIndicador varchar(4000),
@.ecuacion varchar(4000),
@.numerador int,
@.denominador int,
@.lvaIndicador2 varchar(4000),
@.ecuacion2 varchar(4000),
@.numerador2 int,
@.denominador2 int,
@.porcentajeCumple numeric

EXEC @.return_value = [dbo].[SP_CUMPLENCONNIVEL]
@.nmtipoCompetencia = N'12,13,14',
@.nmdivisioncorporativa = N'18,19,20',
@.nmciclo = 9,
@.nmcicloCompara = 12,
@.nmempresa = 72,
@.nmcargo = N'20',
@.lvaIndicador = @.lvaIndicador OUTPUT,
@.ecuacion = @.ecuacion OUTPUT,
@.numerador = @.numerador OUTPUT,
@.denominador = @.denominador OUTPUT,
@.lvaIndicador2 = @.lvaIndicador2 OUTPUT,
@.ecuacion2 = @.ecuacion2 OUTPUT,
@.numerador2 = @.numerador2 OUTPUT,
@.denominador2 = @.denominador2 OUTPUT,
@.porcentajeCumple = 60

Could you explain your problem at bit more in deatil, I am not quite sure of somebody understood that right now.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

When I put that query on the screen of reporting services it works fine because it has the multivalue parameters wrote on the query.

But when I try to change the query to this I got some syntax errors

DECLARE @.return_value int,
@.lvaIndicador varchar(4000),
@.ecuacion varchar(4000),
@.numerador int,
@.denominador int,
@.lvaIndicador2 varchar(4000),
@.ecuacion2 varchar(4000),
@.numerador2 int,
@.denominador2 int,
@.porcentajeCumple numeric

EXEC @.return_value = [dbo].[SP_CUMPLENCONNIVEL]
@.nmtipoCompetencia = N'@.nmtipoCompetencia ',
@.nmdivisioncorporativa = N'@.nmdivisioncorporativa',
@.nmciclo = @.nmciclo,
@.nmcicloCompara = @.nmcicloCompara,
@.nmempresa = @.nmempresa,
@.nmcargo = N'@.nmcargo',
@.lvaIndicador = @.lvaIndicador OUTPUT,
@.ecuacion = @.ecuacion OUTPUT,
@.numerador = @.numerador OUTPUT,
@.denominador = @.denominador OUTPUT,
@.lvaIndicador2 = @.lvaIndicador2 OUTPUT,
@.ecuacion2 = @.ecuacion2 OUTPUT,
@.numerador2 = @.numerador2 OUTPUT,
@.denominador2 = @.denominador2 OUTPUT,
@.porcentajeCumple = @.porcentajeCumple

like nmciclo was not declared.

|||Look in the declare syntax, you did not declare the nmciclo parameter. If you want to pass the parameter to the procedure you either have to declare and eventually fill it or pass a static parameter to the procedure.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

I already declared it and put the stored procedure in this way.

DECLARE @.return_value int,
@.lvaIndicador varchar(4000),
@.ecuacion varchar(4000),
@.numerador int,
@.denominador int,
@.lvaIndicador2 varchar(4000),
@.ecuacion2 varchar(4000),
@.numerador2 int,
@.denominador2 int,
@.porcentajeCumple numeric,
@.nmciclo int, @.nmciclocompara int,@.nmempresa int

EXEC @.return_value = [dbo].[SP_CUMPLENCONNIVEL]
@.nmtipoCompetencia = N'@.nmtipoCompetencia ',
@.nmdivisioncorporativa = N'@.nmdivisioncorporativa',
@.nmciclo = @.nmciclo,
@.nmcicloCompara = @.nmcicloCompara,
@.nmempresa = @.nmempresa,
@.nmcargo = N'@.nmcargo',
@.lvaIndicador = @.lvaIndicador OUTPUT,
@.ecuacion = @.ecuacion OUTPUT,
@.numerador = @.numerador OUTPUT,
@.denominador = @.denominador OUTPUT,
@.lvaIndicador2 = @.lvaIndicador2 OUTPUT,
@.ecuacion2 = @.ecuacion2 OUTPUT,
@.numerador2 = @.numerador2 OUTPUT,
@.denominador2 = @.denominador2 OUTPUT,
@.porcentajeCumple = @.porcentajeCumple

Now I got a complex error that must be something inside the stored procedure.

TITLE: Microsoft Report Designer

An error occurred while executing the query.
Must declare the scalar variable "@.nmcargo".
The variable '@.lcuPersona' does not currently have a cursor allocated to it.
Error converting data type nvarchar to numeric.

ADDITIONAL INFORMATION:

Must declare the scalar variable "@.nmcargo".
The variable '@.lcuPersona' does not currently have a cursor allocated to it.
Error converting data type nvarchar to numeric. (Microsoft SQL Server, Error: 137)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=137&LinkId=20476

BUTTONS:

OK

The strange thing it works fine with the parameters wrote there.. Do you want me to paste the 500 lines stored procedure?

|||Hi,

no I definetely don′t want you to post that code here, but if there is no way to avoid it :-)

Why are you doing this thing here: N'@.nmcargo',

Seems like you want to pass a variable, but you use it like a string ?!

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||nmcargo and the parameters that have the N in front of it are multivalue parameters that must be passed separated by commas|||

Re ... SQL Server Rerporting Services SSRS "Must declare the scalar variable @.variable"

I had the VS report working fine and as soon as I deployed the report and ran it under Report Manager ... Boom error as above.

Thanks to hint on a completely unrelated Google groups post, I checked the DS (Data Source) being used by VS (Visual Studio) and the one being used by RM...

Guess what - VS DS was using a SQL Server Connection string and the RM DS was using ODBC or OLEDB ...

Changed the DS via RM to be SQL Server and now it all works fine ...

Gotta love the quality of those error message -