Multi-Value Parameter

I have a stored procedure im passing into Reporting Services. Only problem is , What do i need to change to allow the user to select more then one value. I already know what to do on the reporting services side, but it keeps erroring with the data source IE my stored procedure. Here's the code:

Code Snippet

SE [RC_STAT]

GO

/****** Object: StoredProcedure [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY] Script Date: 09/05/2007 13:49:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY]

(@.Region int = Null)

AS

BEGIN

SELECT Budget_Reporting_Detail.Budget_Report_Detail_Datetime, Budget_Reporting_Detail.Budget_Report_Detail_Document_Type,

Budget_Reporting_Detail.Budget_Report_Detail_Code, Budget_Reporting_Detail.Budget_Report_Detail_Description,

ISNULL(Budget_Reporting_Detail.Budget_Report_Detail_Amount, 0) AS Actual, Budget_Reporting_Detail.Budget_Report_Detail_Qty,

Budget_Reporting_Detail.Budget_Report_Detail_Responsible, Territory.Name+'('+Code+')' as [Name], Region.Region, Round((Forecast.Budget_Amount/13),2) AS Budget,

Forecast.Budget_Type_Code, Forecast.Budget_Year, Budget_Forecast_Period,

Forecast.SalesPerson_Purchaser_Code

FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting_Detail AS Budget_Reporting_Detail RIGHT OUTER JOIN

RC_DWDB_INSTANCE_1.dbo.Region AS Region RIGHT OUTER JOIN

(SELECT Budget_Type_Code, Budget_Year, SalesPerson_Purchaser_Code, Budget_Amount

FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget

) AS Forecast INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Territory AS Territory INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Sales_Responsible AS Territory_In_Sales_Responsible ON

Territory.Code = Territory_In_Sales_Responsible.Territory_Code INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region AS Territory_In_Region ON Territory_In_Region.Territory_Code = Territory.Code ON

Forecast.SalesPerson_Purchaser_Code = Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code ON

Region.Region_Key = Territory_In_Region.Region_Key ON Budget_Reporting_Detail.Budget_Type_Code = Forecast.Budget_Type_Code AND

Budget_Reporting_Detail.Budget_Year = Forecast.Budget_Year AND

Budget_Reporting_Detail.SalesPerson_Purchaser_Code = Forecast.SalesPerson_Purchaser_Code

WHERE (Region.Region_Key IN( @.Region)) AND (Forecast.Budget_Year = 2007)

END

what am i doing wrong?

You can concatenate the multi values in a comma delimited string and pass the string to the stored procedure. you can create a function in SS to split the string and return a table.

Expression used to populate the parameter

=Join(Parameters! <ParameterName> .Value,", ")

Arrays and Lists in SQL Server

http://www.sommarskog.se/arrays-in-sql.html

AMB

Multi-table UDF not returning all rows

I've been tearing my hair out over this UDF. The code works within a
stored procedure and also run ad-hoc against the database, but does not
run properly within my UDF. We've been using the SP, but I do need a
UDF instead now.

All users, including branch office, sub-companies and companies and so
on up the lines are in the same table. I need a function which returns
a row for each level, eventually getting to the master company all the
way at the top, but this UDF acts as though it can't enter the loop and
only inserts the @.userID and @.branchID rows. I have played with the
WHILE condition to no avail.

Any ideas on what I am missing?

(Running against SQL Server 2000)
----------------

ALTER FUNCTION udfUplineGetCompany (@.userID int)

RETURNS @.upline table (companyID int, companyname varchar(100), info
varchar(100))
AS
BEGIN

DECLARE @.branchID int
DECLARE @.companyID int
DECLARE @.tempID int

--Insert the original user data
INSERT INTO @.upline
SELECT tblusersid, companyname, 'userID'
FROM tblusers
WHERE tblusersid = @.userid

SELECT @.branchID = tblUsers.tblUsersID
FROM tblUsers
INNER JOIN tblUsersUsersLnk
ON tblUsers.tblUsersID = tblUsersUsersLnk.tblUsersID_Parent
WHERE tblUsersUsersLnk.tblUsersID_Child = @.userid

--Up one level
INSERT INTO @.upline
SELECT tblusersid, companyname, 'branchID'
FROM tblusers
WHERE tblusersid = @.branchid

SET @.tempID = @.branchID

WHILE @.@.ROWCOUNT <> 0
BEGIN
SELECT @.companyID = tblUsers.tblUsersID
FROM tblUsers
INNER JOIN tblUsersUsersLnk
ON tblUsers.tblUsersID = tblUsersUsersLnk.tblUsersID_Parent
WHERE tblUsersUsersLnk.tblUsersID_Child = @.tempID
AND tblUsersId <> 6

--Insert a row for each level up
INSERT INTO @.upline
SELECT tblusersid, companyname, 'companyID'
FROM tblusers
WHERE tblusersid = @.companyID

SET @.tempID = @.companyID

END

RETURN

ENDWell, if I try to run in as an ad-hoc query, it doesn't run properly at
all.

The use of @.@.rowcount is bound to go wrong. Also, the INSERT into the
table variable will be executed regardless of a match (or miss) of the
parent selection.

You should check the @.@.rowcount immediately after the "SELECT @.CompanyID
= ..." statement. If you do not want to handle the @.@.rowcount result at
that point, then make sure you move the @.@.rowcount status to a variable
immediately after the SELECT statement.

Gert-Jan

P.S. If you have proper primary key/foreign keys in place, then joining
to the tblUsers table in the SELECT statement is unnecessary.

nicolec@.octitle.com wrote:
> I've been tearing my hair out over this UDF. The code works within a
> stored procedure and also run ad-hoc against the database, but does not
> run properly within my UDF. We've been using the SP, but I do need a
> UDF instead now.
> All users, including branch office, sub-companies and companies and so
> on up the lines are in the same table. I need a function which returns
> a row for each level, eventually getting to the master company all the
> way at the top, but this UDF acts as though it can't enter the loop and
> only inserts the @.userID and @.branchID rows. I have played with the
> WHILE condition to no avail.
> Any ideas on what I am missing?
> (Running against SQL Server 2000)
> ----------------
> ALTER FUNCTION udfUplineGetCompany (@.userID int)
> RETURNS @.upline table (companyID int, companyname varchar(100), info
> varchar(100))
> AS
> BEGIN
> DECLARE @.branchID int
> DECLARE @.companyID int
> DECLARE @.tempID int
> --Insert the original user data
> INSERT INTO @.upline
> SELECT tblusersid, companyname, 'userID'
> FROM tblusers
> WHERE tblusersid = @.userid
> SELECT @.branchID = tblUsers.tblUsersID
> FROM tblUsers
> INNER JOIN tblUsersUsersLnk
> ON tblUsers.tblUsersID = tblUsersUsersLnk.tblUsersID_Parent
> WHERE tblUsersUsersLnk.tblUsersID_Child = @.userid
> --Up one level
> INSERT INTO @.upline
> SELECT tblusersid, companyname, 'branchID'
> FROM tblusers
> WHERE tblusersid = @.branchid
> SET @.tempID = @.branchID
> WHILE @.@.ROWCOUNT <> 0
> BEGIN
> SELECT @.companyID = tblUsers.tblUsersID
> FROM tblUsers
> INNER JOIN tblUsersUsersLnk
> ON tblUsers.tblUsersID = tblUsersUsersLnk.tblUsersID_Parent
> WHERE tblUsersUsersLnk.tblUsersID_Child = @.tempID
> AND tblUsersId <> 6
> --Insert a row for each level up
> INSERT INTO @.upline
> SELECT tblusersid, companyname, 'companyID'
> FROM tblusers
> WHERE tblusersid = @.companyID
> SET @.tempID = @.companyID
> END
> RETURN
> END|||Sometimes you look at something SO long you don't see the obvious...
thanks for the pointer, that worked perfect. For future newsgroup
reference, the new function text is at the bottom.

>P.S. If you have proper primary key/foreign keys in place, then joining
to the tblUsers table in the SELECT statement is unnecessary.

No keys anywhere in this database and it's driving me nuts. Every join
must be explicit.

----------------

ALTER FUNCTION udfUplineGetCompany (@.userID int)

RETURNS @.upline table (companyID int, companyname varchar(100), info
varchar(100), rows int)
AS
BEGIN

DECLARE @.branchID int
DECLARE @.companyID int
DECLARE @.tempID int
DECLARE @.rows int

SET @.rows = 1

INSERT INTO @.upline
SELECT tblusersid, companyname, 'userID', @.rows
FROM tblusers
WHERE tblusersid = @.userid

SELECT @.branchID = tblUsers.tblUsersID
FROM tblUsers
INNER JOIN tblUsersUsersLnk
ON tblUsers.tblUsersID = tblUsersUsersLnk.tblUsersID_Parent
WHERE tblUsersUsersLnk.tblUsersID_Child = @.userid

INSERT INTO @.upline
SELECT tblusersid, companyname, 'branchID', @.rows
FROM tblusers
WHERE tblusersid = @.branchid

SET @.tempID = @.branchID

WHILE @.rows = 1
BEGIN
SELECT @.companyID = tblUsers.tblUsersID
FROM tblUsers
INNER JOIN tblUsersUsersLnk
ON tblUsers.tblUsersID = tblUsersUsersLnk.tblUsersID_Parent
WHERE tblUsersUsersLnk.tblUsersID_Child = @.tempID
AND tblUsersId <> 6

SET @.rows = @.@.rowcount

INSERT INTO @.upline
SELECT tblusersid, companyname, 'companyID', @.rows
FROM tblusers
WHERE tblusersid = @.companyID
AND @.rows = 1

SET @.tempID = @.companyID

END

RETURN

END

multiquery in a store procedure...

Hi, here is the problem...

I must create a store procedure that do the following...

1. select id_name,...'some other field' from table 'data' where 'criteria' =
@.parameter

2. use the result of the first selection to create another selection
combining that results with the table 'name' where id_name is null in the
result selection...

In other word I have a first query that use a passed parameter, the result
of that query should be combined this another query, and the result should
be return from the store procedure...

I can't realize how to write this 'simple' problem... can anyone help me?

Many thanks,
AlexAlessandro (giumalex@.tiscali.it) writes:
> I must create a store procedure that do the following...
> 1. select id_name,...'some other field' from table 'data' where
> 'criteria' = @.parameter
> 2. use the result of the first selection to create another selection
> combining that results with the table 'name' where id_name is null in the
> result selection...
> In other word I have a first query that use a passed parameter, the result
> of that query should be combined this another query, and the result should
> be return from the store procedure...

A simple solution is to use temp tables or table variables.

However, of you often can do this with a derived table, which conceptually
can be seen as a temp table, but physically it is never materialized.

Here is an example:

SELECT C.*
FROM Northwind..Customers C
JOIN (SELECT CustomerID
FROM Northwind..Orders
GROUP BY CustomerID
HAVING COUNT(*) > 20) O ON C.CustomerID = O.CustomerID

Lists all customer informations about customers that have placed more than
20 orders.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Alessandro" <giumalex@.tiscali.it> wrote in message news:<g8tkc.30640$eR5.16238@.tornado.fastwebnet.it>...
> Hi, here is the problem...
>...
> I can't realize how to write this 'simple' problem... can anyone help me?
> Many thanks,
> Alex

Hi Alex.. to work with SQL , query and paramter look at this site :

Http://www.RealTimeInformatica.it/Stany

Bye Bye ...

ps: anche in Italiano !;)

multiquery in a store procedure...

Hi, here is the problem...

I must create a store procedure that do the following...

1. select id_name,...'some other field' from table 'data' where 'criteria' =
@.parameter

2. use the result of the first selection to create another selection
combining that results with the table 'name' where id_name is null in the
result selection...

In other word I have a first query that use a passed parameter, the result
of that query should be combined this another query, and the result should
be return from the store procedure...

I can't realize how to write this 'simple' problem... can anyone help me?

Many thanks,
AlexAlessandro (giumalex@.tiscali.it) writes:
> I must create a store procedure that do the following...
> 1. select id_name,...'some other field' from table 'data' where
> 'criteria' = @.parameter
> 2. use the result of the first selection to create another selection
> combining that results with the table 'name' where id_name is null in the
> result selection...
> In other word I have a first query that use a passed parameter, the result
> of that query should be combined this another query, and the result should
> be return from the store procedure...

A simple solution is to use temp tables or table variables.

However, of you often can do this with a derived table, which conceptually
can be seen as a temp table, but physically it is never materialized.

Here is an example:

SELECT C.*
FROM Northwind..Customers C
JOIN (SELECT CustomerID
FROM Northwind..Orders
GROUP BY CustomerID
HAVING COUNT(*) > 20) O ON C.CustomerID = O.CustomerID

Lists all customer informations about customers that have placed more than
20 orders.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Multiple while fetch cursor code

I seem to have a few problems with the below double cursor procedure. Probably due to the fact that I have two while loops based on fetch status. Or?

What I want to do is select out a series of numbers in medlemmer_cursor(currently set to only one number, for which I know I get results) and for each of these numbers select their MCPS code and gather these in a single string.

For some reason the outpiut (the insert into statement) returns the correct number 9611 but the second variable @.instrumentlinje remains empty.

If I test the select clause for 9611, it gets 4 lines. So to me its like the "SELECT @.instrumentlinje = @.instrumentlinje + ' ' + @.instrument" statement doesn't execute.

DELETE FROM ALL_tbl_instrumentkoder

DECLARE @.medlem int
DECLARE @.instrument varchar(10)
DECLARE @.instrumentlinje varchar(150)

DECLARE medlemmer_cursor CURSOR FOR
SELECT medlemsnummer
FROM ket.ALL_tbl_medlemsinfo (NOLOCK)
WHERE medlemsnummer = 9611

DECLARE instrumenter_cursor CURSOR FOR
SELECT [MCPS Kode]
FROM Gramex_DW.dbo.Instrumentlinie (NOLOCK)
WHERE Medlemsnummer = @.medlem

OPEN medlemmer_cursor

FETCH NEXT FROM medlemmer_cursor INTO @.medlem

WHILE @.@.FETCH_STATUS = 0
BEGIN

OPEN instrumenter_cursor
FETCH NEXT FROM instrumenter_cursor INTO @.instrument

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.instrumentlinje = @.instrumentlinje + ' ' + @.instrument
FETCH NEXT FROM instrumenter_cursor INTO @.instrument
END

CLOSE instrumenter_cursor

INSERT INTO ALL_tbl_instrumentkoder VALUES(@.medlem, @.instrumentlinje)

FETCH NEXT FROM medlemmer_cursor INTO @.medlem

END

CLOSE medlemmer_cursor
DEALLOCATE medlemmer_cursor
DEALLOCATE instrumenter_cursorWell, I suspect the problem is related to referencing a variable in your cursor definition, but you shouldn't be using a cursor anyway.

Here is a simpler (non-cursor) method:

First, create this function:
create function dbo.instrumentlinje(@.medlem int)
returns varchar(4000) as
begin
declare @.instrumentlinje
select @.instrumentlinje = isnull(@.instrumentlinje + ' ', '') + MCPS Kode
from Gramex_DW.dbo.Instrumentlinie (NOLOCK)
WHERE Medlemsnummer = @.medlem

return @.instrumentlinje
end

Then, run this code:
insert into ALL_tbl_instrumentkoder
(medlem,
instrumentlinje)
select medlemsnummer,
dbo.instrumentlinje(medlem)
FROM ket.ALL_tbl_medlemsinfo (NOLOCK)
WHERE medlemsnummer = 9611

Warning! Not tested for syntax errors, and you may need to edit object ownership.|||Basically the same way I did it in Access.. just a greenhorn when it comes to SQL-server.

Thanks man :-)|||TSQL is similar to Access SQL, though there are a few syntactical differences. The concept of avoiding cursors and loops in favor of set-based operations is the same, though.

Multiple Updates In Store Procedure?

I am a total newbie to SQL Server, but have a good idea about how things operate..

I have a "job" that runs multiple (10) update queries for me in different tables.

Can I create a stored procedure to do these so that I can make one call to them?

If not, how can I call the job to start external to MS SQL?>>Can I create a stored procedure to do these so that I can make one call to them?

ofcourse. if you have all the required parameters you definetely can.

hth

multiple transaction ?

I have a question regarding multiple transactions. I
would like to create a main stored procedure that calls
several other stored procedures. I would like to use
transactions when I write all of my SP's but am unsure
how it would works. I am running into problems when a
rollback occurs when i have multiple transactions. Is
there a standard way of setting up a commit/rollback
procedure in all SP's? I always use the following but it
does not seem to work correctly.
BEGIN TRANSACTION
IF @.ErrorCount = 0
COMMIT TRANSATION
ELSE
ROLLBACK TRANSACTION
Do i need something in here to look at the transaction
count?
Any help would be appreciated.
Thanks
B.A.I have to assume that @.ErrorCount is something of your doing so I will also
assume it is getting this correctly. When you issue a ROLLBACK, regardless
of where it's issued, all teh open transactions will be rolled back. So you
want to test to see if @.@.TRANCOUNT is greater than 0 before issuing the
rollbck otherwise you will get an error.
Andy
"B.A. Baracus" <JCoxEUP@.hotmail.com> wrote in message
news:014c01c34f12$4d4b6c20$a501280a@.phx.gbl...
> I have a question regarding multiple transactions. I
> would like to create a main stored procedure that calls
> several other stored procedures. I would like to use
> transactions when I write all of my SP's but am unsure
> how it would works. I am running into problems when a
> rollback occurs when i have multiple transactions. Is
> there a standard way of setting up a commit/rollback
> procedure in all SP's? I always use the following but it
> does not seem to work correctly.
> BEGIN TRANSACTION
> IF @.ErrorCount = 0
> COMMIT TRANSATION
> ELSE
> ROLLBACK TRANSACTION
> Do i need something in here to look at the transaction
> count?
> Any help would be appreciated.
> Thanks
> B.A.
>

Multiple tables from within a stored procedure - how?

Hi,
I'm currently analyzing SSRS as a potential replacement for Crystal Reports
(and yes, it's definitely going to replace it). I do have one issue,
however... I'm attempting to point SSRS to a stored procedure that returns
four separate tables, but only the first one seems to be returned.
Is there a way to have access to each of the tables within the sproc?
Regards,
ScottNo there is not and this has not changed in RS 2008.
The most you can do is to have a parameter that selects which one you want
and then when calling the stored procedure set that parameter to a constant
value. If used in a report and all four are desired you have to call it four
times.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott McNair" <smcnair@.beachexpress.takethispartout.com> wrote in message
news:Xns9A218ECAC3E7Adonquixote235gmailco@.207.46.248.16...
> Hi,
> I'm currently analyzing SSRS as a potential replacement for Crystal
> Reports
> (and yes, it's definitely going to replace it). I do have one issue,
> however... I'm attempting to point SSRS to a stored procedure that returns
> four separate tables, but only the first one seems to be returned.
> Is there a way to have access to each of the tables within the sproc?
> Regards,
> Scott

Multiple tables from a dataset

Hi all,
Is it possible to use multiple tables being returned from the Stored
Procedure in a dataset on my report. it seems the dataset in the report
layout uses just the first data table.
thanx
regards,
KalpnaI remember reading somewhere in the book "Micrsoft Sql Server 2000 Reporting
Services" that reporting services do NOT support multiple datasets being
returned by stored procs.
-stan
"kalpna" <kalpna.prakash@.grapecity.com> wrote in message
news:u4Im5Q1gEHA.536@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Is it possible to use multiple tables being returned from the Stored
> Procedure in a dataset on my report. it seems the dataset in the report
> layout uses just the first data table.
> thanx
> regards,
> Kalpna
>|||Hi
AnyBody can locate that MSDN link.
Thanks
Amit
"Stan Huff" <no!_spam!_stanhuff@.yhaoo.com> wrote in message
news:#op8SZ6gEHA.1392@.TK2MSFTNGP11.phx.gbl...
> I remember reading somewhere in the book "Micrsoft Sql Server 2000
Reporting
> Services" that reporting services do NOT support multiple datasets being
> returned by stored procs.
> -stan
> "kalpna" <kalpna.prakash@.grapecity.com> wrote in message
> news:u4Im5Q1gEHA.536@.TK2MSFTNGP11.phx.gbl...
> > Hi all,
> >
> > Is it possible to use multiple tables being returned from the Stored
> > Procedure in a dataset on my report. it seems the dataset in the report
> > layout uses just the first data table.
> >
> > thanx
> > regards,
> > Kalpna
> >
> >
>|||kalpna wrote:
> Hi all,
> Is it possible to use multiple tables being returned from the Stored
> Procedure in a dataset on my report. it seems the dataset in the
> report layout uses just the first data table.
Hello Kalpna,
What I experienced by now, is that you get only the data from the first
select statement. So I think you have to create a second datasource, as
I usually do, if i have two tables in my report.
Manfred

Multiple Table Queries

I am having propblems creating a multiple table query for a stored procedure.

Here is what I have..

db1 = transaction database with multipke fields keyed on transid, and membid

db2 = transaction legal details keyed on transid & legalid

db3 = member details keyed on membid & officeid

db4 = office details keyed on officeid & membid

What I am after is a query which will result in ALL details being returned for a particular transaction

ie. transaction detail, legal detail, member detail & office detail...

please helpsomething like...

select db1.*, db2.*, db3.*, db4.*
from db1
join db2 on db1.transid = db2.transid
join db3 on db1.membid = db3.membid
join db4 on db3.officeid = db4.officeid|||Thanks, worked like a charm.

Much Obliged.. have more queries to do for this project, I am sure I will have more posts for you.

multiple table entry in a stored procedure

is there a way when making a stored procedure if i can enter the information in multiple tables?? say the primary key into another table to link the relationship? or should i just pull it out and then put in?

Yes it is possible.

After the first insert call "SCOPE_IDENTITY() " .

This will retrieve the ID number from the newly inserted row. This can be stored in a variable and entered into a second insert :)

Multiple Stored Procedures and Transactions

I have a simple question: Can I have two or more stored procedures that begin transactions but the last stored procedure will commit all transactions if there was no error?

In other words, I have a stored procedure that performs some legacy stuff. I do not want to rewrite that logice, execpt I am putting it into a Stored Procedure since it currently is embedded SQL.

To support newer functionality, I am writing data to additional new tables. However, I do not want the *new* things to occur if there is an error. This is easy enough, I dont call the Stored Procedure for the new functionality if there was an error. However, if there was not an error and the newer stored procedure is called AND there is an error in the new stored procedure, I want to ROLLBACK the changes from the proceeding stored procedures.

To my understanding, I can name transactions but that is only to indicate in the logs what transactions have failed.

I thought about not using transactions for any of the individual stored procedures and calling them from a main stored procedure. The main stored procedure will have a BEGIN TRY and an END TRY (I am using SQL Server 2005) and at the top (right after the try) I will have a BEGIN TRANSACTION. In the BEGIN CATCH I will have a ROLLBACK and at the end I will have a COMMIT. If any of the stored procedures fail at any point it will cause the catch to occur thus rolling back all of my transactions. This would be the easiest way but I still need to deal with the question of, "What happens if any single stored procedure is called?" I guess I could have try and catches in each individual stored procedure for that.

I just want to make sure I am not overlooking something simple. I think I have the right idea by doing it this way I mentioned above. Because this is critical billing processing I just want to have some reassurance that this would work.

Thank

Why don't you add a column to the legacy output to indicate errors. That way nothing is stopped, but you can determine at what point errors start and output to the user, or react accordingly.

|||

Thank you for your response but the problem with that is I want things to stop, I dont want the data in both tables to be out of sync. The only reason we are supporting the older table is the web application is quite huge, very very large and at this point, rewriting legacy code (using classic ASP) to use our new tables would be time consuming. Additionaly, we have several apps wrote in Visual Basic 6 that would need to be rewrote as well to support our newer system. I am developing a new Payment Processing System in C# that writes to the database, and caputres additional data that is useful instead of waiting for end of month. The old system is very very inefficient but until we have the resources to go back and recode it, End of Month Reports still run off of it. These jobs take 2 hours to run (Yeah lots of data) However, the purpose of the new system is to capture some of that data that wasn't avaialble until the end of month. It does this by writing the data that one department needs concering payments and adjustments at the time that action is performed.

However, we cannot have any one table having a different value than the other. If I do it the way you stated then if there is an error, our historical end of month data will be different than data in our new table.

From my understanding, if I wrap everything in a TRY CATCH block in sql server and if any one statment or procedure fails and I am raising the error, the Outermost TRY CATCH BLOCK will also fail, thus braching off to its CATCH BLOCK and inside that CATCH Block I have a rollback. From what I have read, the outermost COMMIT and ROLLBACK are what matters and if it ROLLSBACK then everything rolls back.. I just want to make sure I am understanding this correctly.

Thanks!

John

|||

jgilmore:

Can I have two or more stored procedures that begin transactions but the last stored procedure will commit all transactions if there was no error?

Yes. In fact, that's the default behavior of "nested transactions"

Seehttp://www.codeproject.com/KB/database/sqlservertransactions.aspx and scroll down to "nested transactions" but here's the nutshell answer. Assuming proc1 executes proc2, which contains a begin tran/commit, as long as proc1 wraps the call to proc2 in another begin tran/commit/rollback, a rollback done as part of the outer transaction will in fact roll back the transaction done by proc2, even if proc2's transaction has commited.

For many more articles, just google "sql server nested transaction"

|||

Thank you dbland07666!

I wanted to ensure that this worked as I expected it to.

John

multiple stored procedure...or 1 dynamic procedure?

Ok, so i have this program, and at the moment, it generates an sql statement based on an array of db fields, and an array of values...

my question is this, is there any way to create a stored procedure that has multiple dynamic colums, where the amount of colums could change based on how many are in the array, and therefore passed by parameters...

if this is possible, is it then better the pass both columns and values as parameters, (some have over 50 columns)...or just create a seperate stored procedure for each scenario?? i have no worked out how many this could be, but there is 6 different arrays of colums, 3 possible methods (update, insert and select), and 2 options for each of those 24...so possibly upto 48 stored procs...

this post has just realised how deep in im getting. i might just leave it as it is, and have it done in my application...

but my original question stands, is there any way to add a dynamic colums to a stored proc, but there could be a different number of colums to update or insert into, depending on an array??

Cheers,
JustinHi freefall

Did you read the link Jesse gave you? Read that and you shouldn't need much more tutelage on the use of dynamic SQL.

Just FYI - you would probably find most of the people on here would say you are going the wrong way and shouldn't be looking to use dynamic SQL for this. Instead have a number of hard coded sprocs specific to the tables and actions you want to perform on the tables. This will result in more secure and efficient code that is easy to debug.

HTH|||Hi freefall

Did you read the link Jesse gave you? Read that and you shouldn't need much more tutelage on the use of dynamic SQL.

Just FYI - you would probably find most of the people on here would say you are going the wrong way and shouldn't be looking to use dynamic SQL for this. Instead have a number of hard coded sprocs specific to the tables and actions you want to perform on the tables. This will result in more secure and efficient code that is easy to debug.

HTH

Hi,

Yeah I did have a quick read of the dynamic sql like Jesse provided...and was able to solve the previous problem with it...as i was a bit under the pump when i read it, i did not read the entire thing, and missed anything relating different amounts of dynamic columns...

You have pretty much answered what i was asking anyway, so it looks like im in for a long session of writing stored procs...

Thanks Again,
Justin

Multiple Stored Procedure Execute Together - How?

Hi,
I have multiple stored procedures (SP) running in multiple databases. The
output of all the databases has same column names, same number of columns an
d
column types.
I want to run all these different SP's as one SP and combine the output as
one result.
I tried creating one new SP as SPAll and calling all the SP's in SPAll.
eg.
Create SPAll @.Parameter int
AS
Exec SP1 @.Parameter
Exec SP2 @.Parameter
GO
But when I execute SPAll from 'sql server reporting services' (vs.net), it
executes and displays results from SP1 also. I want to display results from
both SP1 and SP2.
Thanks in advance.Hello GJ.
execute the stored procedures saving the result sets to a temporary table
and then select the results from the temporary table to give you a single
result set
IE:
use Northwind
GO
set nocount on
if object_id('tempdb..#results') is not null drop table #results
create table #results(
index_name sysname,/* Index name. */
index_description varchar(210),/* Index description. */
index_keys nvarchar(2078)/* Table or view column(s) upon which the index is
built. */
)
insert #results(index_name,index_description,in
dex_keys)
exec sp_helpindex @.objname = 'dbo.Categories'
insert #results(index_name,index_description,in
dex_keys)
exec sp_helpindex @.objname = 'dbo.Customers'
select * from #results
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"GJ" wrote:

> Hi,
> I have multiple stored procedures (SP) running in multiple databases. The
> output of all the databases has same column names, same number of columns
and
> column types.
> I want to run all these different SP's as one SP and combine the output as
> one result.
> I tried creating one new SP as SPAll and calling all the SP's in SPAll.
> eg.
> Create SPAll @.Parameter int
> AS
> Exec SP1 @.Parameter
> Exec SP2 @.Parameter
> GO
> But when I execute SPAll from 'sql server reporting services' (vs.net), it
> executes and displays results from SP1 also. I want to display results fro
m
> both SP1 and SP2.
> Thanks in advance.|||Best way, based on what you are telling us:
Create SPAll @.Parameter int
AS
create table #spAllReturn
(
<columns that match procs>
)
insert into #spAllReturn
Exec SP1 @.Parameter
insert into #spAllReturn
Exec SP2 @.Parameter
select * from #spAllReturn
GO
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"GJ" <GJ@.discussions.microsoft.com> wrote in message
news:AA68D163-4681-4794-B4F0-748E72C18760@.microsoft.com...
> Hi,
> I have multiple stored procedures (SP) running in multiple databases. The
> output of all the databases has same column names, same number of columns
> and
> column types.
> I want to run all these different SP's as one SP and combine the output as
> one result.
> I tried creating one new SP as SPAll and calling all the SP's in SPAll.
> eg.
> Create SPAll @.Parameter int
> AS
> Exec SP1 @.Parameter
> Exec SP2 @.Parameter
> GO
> But when I execute SPAll from 'sql server reporting services' (vs.net), it
> executes and displays results from SP1 also. I want to display results
> from
> both SP1 and SP2.
> Thanks in advance.|||GJ
create table #test
(
col ...
....
....
)
insert into #test exec sp1
insert into #test exec sp2
select * from #test
"GJ" <GJ@.discussions.microsoft.com> wrote in message
news:AA68D163-4681-4794-B4F0-748E72C18760@.microsoft.com...
> Hi,
> I have multiple stored procedures (SP) running in multiple databases. The
> output of all the databases has same column names, same number of columns
and
> column types.
> I want to run all these different SP's as one SP and combine the output as
> one result.
> I tried creating one new SP as SPAll and calling all the SP's in SPAll.
> eg.
> Create SPAll @.Parameter int
> AS
> Exec SP1 @.Parameter
> Exec SP2 @.Parameter
> GO
> But when I execute SPAll from 'sql server reporting services' (vs.net), it
> executes and displays results from SP1 also. I want to display results
from
> both SP1 and SP2.
> Thanks in advance.

Multiple Step Stored Procedure

Hi,
I am writing a stored procedure in SQL Server 2000 the will require
multiple commits(?) during its operation. The first group of actions
that it must perform is to modify a table by adding some columns and
altering another. The next step reads data out of the table into a
temporary (in memory) table and does some processing on it and writes
it back to the original source table utilizing the recently added
columns. When I try to put this into a stored procedure, everything
after the first GO statement gets cut off, which in this case is the
guts of the procedure. Does anyone know how how to perform multiple
operatinos like this in one stored procedure?
Thanks in advance
Chris Lieb
CREATE TABLE [HRSC] (
[SEQ_NUM] [float] NULL ,
[HuReSC] [nvarchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
INSERT INTO [HRSC] ([SEQ_NUM], [HuReSC])
VALUES (255, 'TX750201TX769999')
INSERT INTO [HRSC] ([SEQ_NUM], [HuReSC])
VALUES (255, 'OK735001OK735002')
INSERT INTO [HRSC] ([SEQ_NUM], [HuReSC])
VALUES (255, 'LA714000')
INSERT INTO [HRSC] ([SEQ_NUM], [HuReSC])
VALUES (255, 'NM870001NM884001')
----
--
-- Add new columns to the table
ALTER TABLE
[HRSC]
ADD
[Low_State] nvarchar(3) NULL,
[Low_SLIC] nvarchar(3) NULL,
[Low_Building] nvarchar(1) NULL,
[Low_IBI] nvarchar(2) NULL,
[High_State] nvarchar(3) NULL,
[High_SLIC] nvarchar(3) NULL,
[High_Building] nvarchar(1) NULL,
[High_IBI] nvarchar(2) NULL
-- Alter the data type of the HuReSC column to be more space-efficient
ALTER TABLE
[HRSC]
ALTER COLUMN
[HuReSC] nvarchar(18)
GO
-- Prepare the temporary table
DECLARE @.hold table (
[SEQ_NUM] [float] NULL ,
[HuReSC] [nvarchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Low_State] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Low_SLIC] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Low_Building] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Low_IBI] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[High_State] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[High_SLIC] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[High_Building] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[High_IBI] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
-- Populate the temporary table with data
INSERT INTO
@.hold ([SEQ_NUM], [HuReSC])
SELECT
[SEQ_NUM],
[HuReSC]
FROM
[HRSC
-- Parse data for low end of range
UPDATE
@.hold
SET
[Low_State] = SUBSTRING([HuReSC], 1, 2),
[Low_SLIC] = SUBSTRING([HuReSC], 3, 3),
[Low_Building] = SUBSTRING([HuReSC], 6, 1),
[Low_IBI] = SUBSTRING([HuReSC], 7, 2)
-- Parse data for high end of range where needed
UPDATE
@.hold
SET
[High_State] = SUBSTRING([HuReSC], 9, 2),
[High_SLIC] = SUBSTRING([HuReSC], 11, 3),
[High_Building] = SUBSTRING([HuReSC], 14, 1),
[High_IBI] = SUBSTRING([HuReSC], 15, 2)
WHERE
LEN([HuReSC]) > 8
-- Clear the real table
TRUNCATE TABLE [HRSC]
-- Populate the table with the parsed data
INSERT INTO
[HRSC]
SELECT
*
FROM
@.hold
GOSince you know the table structure up front, why on earth are you doing
this:
CREATE TABLE
INSERT data
ALTER TABLE add columns
ALTER TABLE ALTER COLUMN but it really doesn't change
INSERT more data
Why not just
CREATE TABLE
INSERT data
The problem here is that you are saying
CREATE PROCEDURE
...do some stuff...
GO
...do more stuff...
The GO is a batch separator, and hence that is where your CREATE PROCEDURE
is completed. Everything after that has nothing to do with the stored
procedure at all. To avoid getting bitten by this, I always use the
following struct:
CREATE PROCEDURE dbo.whatever
AS
BEGIN
... code...
END
GO
Now, if I inadvertently have a GO inside the stored procedure, it won't run,
unless I have horrible code, because the BEGIN and END do not match up.
Of course, if you remove the GO, you will not be able to refer to
non-existant columns within the same batch because the parser will
read-ahead and try to match your column list to the original CREATE TABLE.
So maybe you could explain why you're creating a table, then inserting data,
then adding nullable columns, then altering a column to be the same as it
was before.
A
"Chris Lieb" <chris.lieb@.gmail.com> wrote in message
news:1133819619.878838.109740@.z14g2000cwz.googlegroups.com...
> Hi,
> I am writing a stored procedure in SQL Server 2000 the will require
> multiple commits(?) during its operation. The first group of actions
> that it must perform is to modify a table by adding some columns and
> altering another. The next step reads data out of the table into a
> temporary (in memory) table and does some processing on it and writes
> it back to the original source table utilizing the recently added
> columns. When I try to put this into a stored procedure, everything
> after the first GO statement gets cut off, which in this case is the
> guts of the procedure. Does anyone know how how to perform multiple
> operatinos like this in one stored procedure?
> Thanks in advance
> Chris Lieb
> CREATE TABLE [HRSC] (
> [SEQ_NUM] [float] NULL ,
> [HuReSC] [nvarchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> INSERT INTO [HRSC] ([SEQ_NUM], [HuReSC])
> VALUES (255, 'TX750201TX769999')
> INSERT INTO [HRSC] ([SEQ_NUM], [HuReSC])
> VALUES (255, 'OK735001OK735002')
> INSERT INTO [HRSC] ([SEQ_NUM], [HuReSC])
> VALUES (255, 'LA714000')
> INSERT INTO [HRSC] ([SEQ_NUM], [HuReSC])
> VALUES (255, 'NM870001NM884001')
> ----
--
> -- Add new columns to the table
> ALTER TABLE
> [HRSC]
> ADD
> [Low_State] nvarchar(3) NULL,
> [Low_SLIC] nvarchar(3) NULL,
> [Low_Building] nvarchar(1) NULL,
> [Low_IBI] nvarchar(2) NULL,
> [High_State] nvarchar(3) NULL,
> [High_SLIC] nvarchar(3) NULL,
> [High_Building] nvarchar(1) NULL,
> [High_IBI] nvarchar(2) NULL
> -- Alter the data type of the HuReSC column to be more space-efficient
> ALTER TABLE
> [HRSC]
> ALTER COLUMN
> [HuReSC] nvarchar(18)
> GO
> -- Prepare the temporary table
> DECLARE @.hold table (
> [SEQ_NUM] [float] NULL ,
> [HuReSC] [nvarchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Low_State] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Low_SLIC] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Low_Building] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Low_IBI] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [High_State] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [High_SLIC] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [High_Building] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [High_IBI] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )
> -- Populate the temporary table with data
> INSERT INTO
> @.hold ([SEQ_NUM], [HuReSC])
> SELECT
> [SEQ_NUM],
> [HuReSC]
> FROM
> [HRSC
> -- Parse data for low end of range
> UPDATE
> @.hold
> SET
> [Low_State] = SUBSTRING([HuReSC], 1, 2),
> [Low_SLIC] = SUBSTRING([HuReSC], 3, 3),
> [Low_Building] = SUBSTRING([HuReSC], 6, 1),
> [Low_IBI] = SUBSTRING([HuReSC], 7, 2)
> -- Parse data for high end of range where needed
> UPDATE
> @.hold
> SET
> [High_State] = SUBSTRING([HuReSC], 9, 2),
> [High_SLIC] = SUBSTRING([HuReSC], 11, 3),
> [High_Building] = SUBSTRING([HuReSC], 14, 1),
> [High_IBI] = SUBSTRING([HuReSC], 15, 2)
> WHERE
> LEN([HuReSC]) > 8
> -- Clear the real table
> TRUNCATE TABLE [HRSC]
> -- Populate the table with the parsed data
> INSERT INTO
> [HRSC]
> SELECT
> *
> FROM
> @.hold
> GO
>|||Aaron Bertrand [SQL Server MVP] wrote:
> Since you know the table structure up front, why on earth are you doing
> this:
> CREATE TABLE
> INSERT data
> ALTER TABLE add columns
> ALTER TABLE ALTER COLUMN but it really doesn't change
> INSERT more data
> Why not just
> CREATE TABLE
> INSERT data
The CREATE TABLE and INSERT statements were jsut so you could get the
original table set up to play with. They are not part of the routine
that I want to perform. Also, the ALTER TABLE statement does actually
change the data type of the column, it's just that the CREATE TABLE
that I gave you had already been effected by the ALTER TABLE, so it was
incorrect. I forget the original data type, but the ALTER TABLE
statement does do something.

> The problem here is that you are saying
> CREATE PROCEDURE
> ...do some stuff...
> GO
> ...do more stuff...
> The GO is a batch separator, and hence that is where your CREATE PROCEDURE
> is completed. Everything after that has nothing to do with the stored
> procedure at all. To avoid getting bitten by this, I always use the
> following struct:
> CREATE PROCEDURE dbo.whatever
> AS
> BEGIN
> ... code...
> END
> GO
> Now, if I inadvertently have a GO inside the stored procedure, it won't ru
n,
> unless I have horrible code, because the BEGIN and END do not match up.
> Of course, if you remove the GO, you will not be able to refer to
> non-existant columns within the same batch because the parser will
> read-ahead and try to match your column list to the original CREATE TABLE.
That is exactly the point: I need to have the ALTER TABLE statements
executed before the rest so that I do not get errors. If the batch is
not committed midway, SQL Server tries to interpret the entire script
before it executes and errors saying that the columns do not exist.

> So maybe you could explain why you're creating a table, then inserting dat
a,
> then adding nullable columns, then altering a column to be the same as it
> was before.
> A
Chris Lieb|||> That is exactly the point: I need to have the ALTER TABLE statements
> executed before the rest so that I do not get errors. If the batch is
> not committed midway, SQL Server tries to interpret the entire script
> before it executes and errors saying that the columns do not exist.
Yep, and that's the way SQL Server works. There are a couple of
workarounds, such as dynamic SQL:
http://www.sommarskog.se/dynamic_sql.html
Were it my project, I would probably find a way to get the data structure
right in the first place. Ideally, I would not be changing a table's
structure every time I call a stored procedure.
A|||Do it in two simple steps:
1) fix schema - do it once, but do it right;
2) fix data - do it as many times as needed without changing the schema.
That way you can set the tables 'in concrete' once and for all and then
focus on transforming data. Or do you need to change the tables over and ove
r
again?
ML
http://milambda.blogspot.com/|||I come into this situation alot. (I do a lot of dynamic sql but don't find
that is my favourite solution.) This is a little clunky, but it has worked
for me.
I would create 4 stored procedures, for instance,
spr_DoSomething_Main
spr_DoSomething_1
spr_DoSomething_2
spr_DoSomething_3
where main just says:
exec spr_DoSomething_1
exec spr_DoSomething_2
exec spr_DoSomething_3
If I have parameters it might look like this:
Create Procedure spr_DoSomething_Main
@.Param1 int,
@.Param2 int,
@.Param3 int
AS
exec spr_DoSomething_1 @.Param1
exec spr_DoSomething_2 @.Param1, @.Param2, @.Param3
exec spr_DoSomething_3 @.Param1
go
Whatever procedure needs whatever parameters gets only them. I also
sometimes do this to use stored procedures like classes for possible code
re-use and all...
"ML" wrote:

> Do it in two simple steps:
> 1) fix schema - do it once, but do it right;
> 2) fix data - do it as many times as needed without changing the schema.
> That way you can set the tables 'in concrete' once and for all and then
> focus on transforming data. Or do you need to change the tables over and o
ver
> again?
>
> ML
> --
> http://milambda.blogspot.com/|||Aaron Bertrand [SQL Server MVP] wrote:
> Yep, and that's the way SQL Server works. There are a couple of
> workarounds, such as dynamic SQL:
> http://www.sommarskog.se/dynamic_sql.html
> Were it my project, I would probably find a way to get the data structure
> right in the first place. Ideally, I would not be changing a table's
> structure every time I call a stored procedure.
> A
OK, maybe a stored procedure isn't really the right route to have
chosen.
I am doing this to try to fix a schema that was created a few years ago
by another person who does not work here any more. I was making the
stored procedure so that once I figured out how to parse all of the
data that was on the development server, all I would have to do is have
the SP transferred to the live server and run and the data would be
fixed for me without making me go through and transform all of the data
manually. Having this on the development server also makes it easy to
fix the data when and if I need to mirror data off of the live server
in case something happens, like an error after a truncate statement
before the contents of the temp table are written back to the real
table.
Chris Lieb|||On 6 Dec 2005 06:50:39 -0800, Chris Lieb wrote:

>Aaron Bertrand [SQL Server MVP] wrote:
>OK, maybe a stored procedure isn't really the right route to have
>chosen.
>I am doing this to try to fix a schema that was created a few years ago
>by another person who does not work here any more. I was making the
>stored procedure so that once I figured out how to parse all of the
>data that was on the development server, all I would have to do is have
>the SP transferred to the live server and run and the data would be
>fixed for me without making me go through and transform all of the data
>manually. Having this on the development server also makes it easy to
>fix the data when and if I need to mirror data off of the live server
>in case something happens, like an error after a truncate statement
>before the contents of the temp table are written back to the real
>table.
>Chris Lieb
Hi Chris,
So this is a one-time job?
Instead of making a stored procedure, make an SQL script file. Store it
on your hard disk, tweak it until you're ready, then either copy and
paste into QA and execute against your prod DB or feed it through
osql.exe or a similar tool.
Unlike stored procedure's, SQL scripts can include "go", since a script
is just a load of commands sent to the server (just as if you were
typing very fast <g> ).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis wrote:
> On 6 Dec 2005 06:50:39 -0800, Chris Lieb wrote:
>
> Hi Chris,
> So this is a one-time job?
Once this is deployed, yes. Until then, it will be run many a time.

> Instead of making a stored procedure, make an SQL script file. Store it
> on your hard disk, tweak it until you're ready, then either copy and
> paste into QA and execute against your prod DB or feed it through
> osql.exe or a similar tool.
> Unlike stored procedure's, SQL scripts can include "go", since a script
> is just a load of commands sent to the server (just as if you were
> typing very fast <g> ).
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
I ended up making a DTS package out of the entire procedure, from
creating the tables, indexes, and triggers to transforming and
inserting the data.
Chris Lieb|||On 7 Dec 2005 14:35:28 -0800, Chris Lieb wrote:
(snip)
>Once this is deployed, yes. Until then, it will be run many a time.
Hi Chris,
In test, I hope <g>.
That's what I meant. A one-time job in the production DB.

>I ended up making a DTS package out of the entire procedure, from
>creating the tables, indexes, and triggers to transforming and
>inserting the data.
That's even better. My lack of experience with DTS is showing, I guess.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Multiple SQL statements in a stored procedure

Hi!

I got 2 stored procedure, proc1 executes proc2,
proc2 does some updates and inserts on different tables ...

proc1:

ALTER PROCEDURE
AS
execute proc2

SELECT * FROM tblFoo
______________________
my problem is, that when executing proc1, I receive the message:

"THE SP executed successfully, but did not return records!"

But I need the resultset from "SELECT * FROM tblFoo" that is executed
at the end of proc1.
I'm not sure, but I think that I solved a similira problem with "set
nocount on", I put it into both SP, but it's still the same ... no
resultset ...

How can I display "SELECT * FROM tblFoo" within a SP, where SQL
statements are executed before?!

Thank you!Err, stupid question but did you check that tblFoo actually has records in
it?

"Peter Neumaier" <Peter.Neumaier@.gmail.com> wrote in message
news:1117053332.823938.254990@.g14g2000cwa.googlegr oups.com...
> Hi!
> I got 2 stored procedure, proc1 executes proc2,
> proc2 does some updates and inserts on different tables ...
>
> proc1:
> ALTER PROCEDURE
> AS
> execute proc2
> SELECT * FROM tblFoo
> ______________________
> my problem is, that when executing proc1, I receive the message:
> "THE SP executed successfully, but did not return records!"
> But I need the resultset from "SELECT * FROM tblFoo" that is executed
> at the end of proc1.
> I'm not sure, but I think that I solved a similira problem with "set
> nocount on", I put it into both SP, but it's still the same ... no
> resultset ...
> How can I display "SELECT * FROM tblFoo" within a SP, where SQL
> statements are executed before?!
> Thank you!

Multiple SP values overriding the desired SP value

I have a stored procedure (lets call him Earl to keep things clear) that executes another sp (lets call him Daryl). Daryl returns a value, and although the last thing the first Earl does is selects a record set to return to the code, the only thing the code can access is values from Daryl. I am using NOCOUNT ON in the beginning of Earl, and NOCOUNT OFF at the end of Earl, but I still get Daryl's values.

I even put NOCOUNT ON / OFF in Daryl, but no such luck. How do I bypass Daryl's values and get to Earl's?

Thanks.

Could you post your code?|||

NOCOUNT only effects the rowcount (as in the message (n row(s) affected), and the global @.@.rowcount) not the actual returning of values.

So, are you saying the rowcount is being updated even with SET NOCOUNT ON ?

|||

no, nothing to do with the row count I guess.

What it is: because the secondary SP, which is called by the first, uses a select statement, that data generated from the select is being returned from the primary SP, and not the intended data.

Here is a boiled down version of the SP:

alter procedure earl
as
begin

insert into table (x, y, z) values ('a', 1, 'c')
@.id = @.@.identity

exec daryl(@.id )

select * from table where id = @.id

end

alter daryl
@.id int
as
begin
insert into joiningtable (x, y, z) values (@.id, 1, 'c')

@.error = @.@.error

if(@.error > 0)
select @.error as result
else
select @.@.identity as result

end

so the output from Earl is @.@.identity from Daryl, and not "select * from table where id = @.id " from Earl.

Daryl needs to return data, because this SP can be called directly from another function if @.id is known.

|||Earl will return the intended records in the second result set. So you can try to reach the Next resultset.|||

Yes, this will return two result sets.

What are you using on the client side to recieve these results? If try this from sqlcmd\osql\SSMS, you will see both result sets.

Multiple set result from a stored procedure as the data source

My stored procedure generates two results:
1) declares a variable executes a view to obtain date driven data which has
a begin and end
2) selects the data associated with date driven info
When RS executes the stored procedure I only get the first set results (the
date information).
Can RS handle multiple results from a stored procedure? If so is there any
information to read.
I can change my stored procedure with a couple joins but I am curious how RS
works (I'm a newbee).
SincerelyRS only works with the first resultset.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
news:CDADB1FA-43D3-40A0-BD94-CFD98F9FAFCE@.microsoft.com...
> My stored procedure generates two results:
> 1) declares a variable executes a view to obtain date driven data which
has
> a begin and end
> 2) selects the data associated with date driven info
> When RS executes the stored procedure I only get the first set results
(the
> date information).
> Can RS handle multiple results from a stored procedure? If so is there any
> information to read.
> I can change my stored procedure with a couple joins but I am curious how
RS
> works (I'm a newbee).
> Sincerely|||Sorry...RS doesn't support multiple result sets.
Adrian M.
"HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
news:CDADB1FA-43D3-40A0-BD94-CFD98F9FAFCE@.microsoft.com...
> My stored procedure generates two results:
> 1) declares a variable executes a view to obtain date driven data which
> has
> a begin and end
> 2) selects the data associated with date driven info
> When RS executes the stored procedure I only get the first set results
> (the
> date information).
> Can RS handle multiple results from a stored procedure? If so is there any
> information to read.
> I can change my stored procedure with a couple joins but I am curious how
> RS
> works (I'm a newbee).
> Sincerely|||Thanks for the info
"Adrian M." wrote:
> Sorry...RS doesn't support multiple result sets.
> Adrian M.
> "HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
> news:CDADB1FA-43D3-40A0-BD94-CFD98F9FAFCE@.microsoft.com...
> > My stored procedure generates two results:
> > 1) declares a variable executes a view to obtain date driven data which
> > has
> > a begin and end
> > 2) selects the data associated with date driven info
> >
> > When RS executes the stored procedure I only get the first set results
> > (the
> > date information).
> >
> > Can RS handle multiple results from a stored procedure? If so is there any
> > information to read.
> >
> > I can change my stored procedure with a couple joins but I am curious how
> > RS
> > works (I'm a newbee).
> >
> > Sincerely
>
>

multiple series on line chart

Hello,

I want to display more than one series on a line chart. I'm producing a resultset from a stored procedure which is like this:

Type Value Date

TypeA 0.07 01/06/2006

TypeB 0.08 01/06/2006

TypeC 0.08 01/06/2006

TypeA 0.08 02/06/2006

TypeB 0.09 02/06/2006

TypeC 0.09 02/06/2006

TypeA 0.09 03/06/2006

TypeB 0.10 03/06/2006

TypeC 0.11 03/06/2006

I want a seperate series for each of TypeA, TypeB, TypeC . Is it possible to doctor the resultset to give each Type (and Value) as unique columns? Or is there another way? Or do I have to shell out for Dundas Charts :) !

Thanks in advance for any help.

Sorry, I'm new to RS - I had overlooked the 'Series' area on the layout tab. Can it just be a matter of dropping the 'Type' field there? I'm not at my desk just now but will try in the morning - if someone feels like confirming meantime that would be great.|||

Dragging the 'Type' field from the dataset onto the series area on the layout tab gives me curves for TypeA, TypeB,TypeC. However the dates on the horizontal axis are all wrong 'Year' and 'Month' groups are OK but 'Days' are back to front (I have 3 category groups) - I need this because sometimes the dataset crosses more than 1 year. Anyone ?

|||All problems solved. In case anyone has similar problems - the issue was the dataset - the dates were not uniform for each Type ie.each series had a different set of dates.Once I got the dates matching everything was OK.

Multiple selects in sp

Hi
In a stored procedure, is it possible to check if an input parameter value
has been supplied by user? Furthermore if a parameter's value is supplied
then execute one select statement, if no value is supplied then use a second
select?
Thanks
Regards
try this:
create proc myproc
param int null
as
declare @.sql varchar(100)
if param is null
set @.sql = '...' -- your first select
else
set @.sql = '...' -- your second select
exec (@.sql)
go
|||Ok, I have this sp (attached). Still can't make it work.
Thanks
Regards
"vuht2000" <vuht2000@.yahoo.com> wrote in message
news:1124415729.139947.214020@.g44g2000cwa.googlegr oups.com...
> try this:
> create proc myproc
> param int null
> as
> declare @.sql varchar(100)
> if param is null
> set @.sql = '...' -- your first select
> else
> set @.sql = '...' -- your second select
> exec (@.sql)
> go
>
begin 666 SQLQuery.sql
M__YS`&4`= `@.`$$`3@.!3`$D`7P!.`%4`3 !,`%,`( !/`$X`#0`*`',`90!T
M`" `40!5`$\`5 !%`$0`7P!)`$0`10!.`%0`20!&`$D`10!2`" `3P!.``T`
M"@.!G`&\`#0`*``T`"@.!!`$P`5 !%`%(`( !0`%(`3P!#`$4`1 !5`%(`10`@.
M`%L`9 !B`&\`70`N`%L`0P!L`&D`90!N`'0`<P!3`&4`; !E`&,`= !#`&\`
M;0!M`&$`;@.!D`%T`#0`*`$ `0P!O`&T`< !A`&X`>0!4`'D`< !E`" `=@.!A
M`'(`8P!H`&$`<@.`H`#4`, `I`"P`#0`*`$ `4P!T`&$`= !U`',`( !V`&$`
M<@.!C`&@.`80!R`"@.`-0`P`"D`+ `-``H`0 !"`'4`<P!I`&X`90!S`',`1 !I
M`'8`:0!S`&D`;P!N`" `=@.!A`'(`8P!H`&$`<@.`H`#4`, `I`" `3@.!5`$P`
M3 `-``H`00!3``T`"@.!3`$4`5 `@.`$X`3P!#`$\`50!.`%0`( !/`$X`.P`-
M``H`1 !%`$,`3 !!`%(`10`@.`$ `<P!Q`&P`( !V`&$`<@.!C`&@.`80!R`"@.`
M,0`P`# `*0`-``H`20!&`" `0 !"`'4`<P!I`&X`90!S`',`1 !I`'8`:0!S
M`&D`;P!N`" `:0!S`" `3@.!5`$P`3 `-``H`( `@.`%,`10!4`" `0 !S`'$`
M; `@.`#T`( `G`%,`10!,`$4`0P!4`" `20!$`"P`( !#`&\`;0!P`&$`;@.!Y
M`"P`( !0`&$`<@.!E`&X`= !#`&\`;0!P`&$`;@.!Y`"P`( !!`&0`9 !R`&4`
M<P!S`"P`( !#`&\`=0!N`'0`>0`L`" `4 !O`',`= !C`&\`9 !E`"P`( !#
M`&\`=0!N`'0`<@.!Y`"P`( !4`&4`; `L`" `1@.!A`'@.`+ `@.`$4`30!A`&D`
M; `L`" `5P!E`&(`+ `@.`$D`;@.!V`&\`:0!C`&4`0P!O`&T`< !A`&X`>0`L
M`" `20!N`'8`;P!I`&,`90!!`&0`9 !R`&4`<P!S`"P`( !)`&X`=@.!O`&D`
M8P!E`$,`;P!U`&X`= !Y`"P`( !)`&X`=@.!O`&D`8P!E`% `;P!S`'0`8P!O
M`&0`90`L`" `20!N`'8`;P!I`&,`90!#`&\`=0!N`'0`<@.!Y`"P`( !"`'4`
M<P!I`&X`90!S`',`5 !Y`' `90`L`" `4P!T`&$`= !U`',`+ `@.`%,`= !A
M`'0`=0!S`$0`80!T`&4`+ `@.`%,`;P!U`'(`8P!E`"P`( !3`&\`=0!R`&,`
M90!$`&$`= !E`"P`( !#`&\`;0!P`&$`;@.!Y`%0`>0!P`&4`+ `@.`$(`; !O
M`&,`:P!E`&0`+ `@.`$(`; !O`&,`:P!E`&0`1 !E`' `80!R`'0`;0!E`&X`
M= `L`" `0@.!L`&\`8P!K`&4`9 !$`&$`= !E`"P`( !"`&P`;P!C`&L`90!D
M`%(`90!A`',`;P!N`"P`( !"`'4`<P!I`&X`90!S`',`00!R`&4`80`L`" `
M4P!T`&$`9@.!F`$<`<@.!A`&0`90`Q`"P`( !3`'0`80!F`&8`1P!R`&$`9 !E
M`#(`+ `@.`%,`= !A`&8`9@.!'`'(`80!D`&4`,P`L`" `4P!T`&$`9@.!F`$<`
M<@.!A`&0`90`T`"P`( !#`&P`:0!E`&X`= !.`&\`= !E`',`+ `@.`$8`:0!N
M`&$`;@.!C`&D`80!L`$,`;P!M`&T`90!N`'0`<P`L`" `4P!P`&4`8P!I`&$`
M; !2`&4`<0!U`&D`<@.!M`&4`;@.!T`',`+ `@.`%4`;@.!I`&8`;P!R`&T`<P!.
M`&4`90!D`&4`9 `L`" `00!C`&,`;P!U`&X`= !2`&4`9@.!E`'(`90!N`&,`
M90!.`&\`+ `@.`$,`;P!M`' `80!N`'D`4@.!E`&<`:0!S`'0`<@.!A`'0`:0!O
M`&X`3@.!O`"P`( !3`'4`< !P`&P`:0!E`'(`3@.!O`"P`( !6`&4`;@.!T`'4`
M<@.!E`$0`90!B`'0`;P!R`$X`;P`L`" `4 !A`'D`;0!E`&X`= !4`&4`<@.!M
M`',`+ `@.`%,`= !A`'0`90!M`&4`;@.!T`$$`9 !D`'(`90!S`',`+ `@.`$4`
M=@.!E`&X`= !.`&\`= !E`',`+ `@.`$0`:0!R`&4`8P!T`&D`;P!N`',`+ `@.
M`$T`80!P`% `80!T`&@.`+ `@.`%,`;P!R`'0`1P!R`&\`=0!P`"P`( !3`&\`
M<@.!T`"P`( !$`&$`= !E`$,`<@.!E`&$`= !E`&0`+ `@.`$0`80!T`&4`30!O
M`&0`:0!F`&D`90!D`"P`( !#`'(`90!A`'0`90!D`$(`>0`L`" `30!O`&0`
M:0!F`&D`90!D`$(`>0`L`" `=0!P`',`:0!Z`&4`7P!T`',`( `G`" `)@.`@.
M``T`"@.`@.`" `( `@.`" `( `@.`" `( `@.`" `( `@.`"<`1@.!2`$\`30`@.`&0`
M8@.!O`"X`0P!L`&D`90!N`'0`<P`@.`"<`( `F`" `#0`*`" `( `@.`" `( `@.
M`" `( `@.`" `( `@.`" `)P!7`$@.`10!2`$4`( `H`$,`;P!M`' `80!N`'D`
M5 !Y`' `90`@.`&P`:0!K`&4`( ! `$,`;P!M`' `80!N`'D`5 !Y`' `90`@.
M`"8`( `B`"4`(@.`I`" `00!.`$0`( `G`" `)@.`@.``T`"@.`@.`" `( `@.`" `
M( `@.`" `( `@.`" `( `@.`" `( `@.`" `( `@.`"<`* !3`'0`80!T`'4`<P`@.
M`&P`:0!K`&4`( ! `%,`= !A`'0`=0!S`" `)@.`@.`"(`)0`B`"D`)P`-``H`
M10!,`%,`10`-``H`( `@.`%,`10!4`" `0 !S`'$`; `@.`#T`( `G`%,`10!,
M`$4`0P!4`" `20!$`"P`( !#`&\`;0!P`&$`;@.!Y`"P`( !0`&$`<@.!E`&X`
M= !#`&\`;0!P`&$`;@.!Y`"P`( !!`&0`9 !R`&4`<P!S`"P`( !#`&\`=0!N
M`'0`>0`L`" `4 !O`',`= !C`&\`9 !E`"P`( !#`&\`=0!N`'0`<@.!Y`"P`
M( !4`&4`; `L`" `1@.!A`'@.`+ `@.`$4`30!A`&D`; `L`" `5P!E`&(`+ `@.
M`$D`;@.!V`&\`:0!C`&4`0P!O`&T`< !A`&X`>0`L`" `20!N`'8`;P!I`&,`
M90!!`&0`9 !R`&4`<P!S`"P`( !)`&X`=@.!O`&D`8P!E`$,`;P!U`&X`= !Y
M`"P`( !)`&X`=@.!O`&D`8P!E`% `;P!S`'0`8P!O`&0`90`L`" `20!N`'8`
M;P!I`&,`90!#`&\`=0!N`'0`<@.!Y`"P`( !"`'4`<P!I`&X`90!S`',`5 !Y
M`' `90`L`" `4P!T`&$`= !U`',`+ `@.`%,`= !A`'0`=0!S`$0`80!T`&4`
M+ `@.`%,`;P!U`'(`8P!E`"P`( !3`&\`=0!R`&,`90!$`&$`= !E`"P`( !#
M`&\`;0!P`&$`;@.!Y`%0`>0!P`&4`+ `@.`$(`; !O`&,`:P!E`&0`+ `@.`$(`
M; !O`&,`:P!E`&0`1 !E`' `80!R`'0`;0!E`&X`= `L`" `0@.!L`&\`8P!K
M`&4`9 !$`&$`= !E`"P`( !"`&P`;P!C`&L`90!D`%(`90!A`',`;P!N`"P`
M( !"`'4`<P!I`&X`90!S`',`00!R`&4`80`L`" `4P!T`&$`9@.!F`$<`<@.!A
M`&0`90`Q`"P`( !3`'0`80!F`&8`1P!R`&$`9 !E`#(`+ `@.`%,`= !A`&8`
M9@.!'`'(`80!D`&4`,P`L`" `4P!T`&$`9@.!F`$<`<@.!A`&0`90`T`"P`( !#
M`&P`:0!E`&X`= !.`&\`= !E`',`+ `@.`$8`:0!N`&$`;@.!C`&D`80!L`$,`
M;P!M`&T`90!N`'0`<P`L`" `4P!P`&4`8P!I`&$`; !2`&4`<0!U`&D`<@.!M
M`&4`;@.!T`',`+ `@.`%4`;@.!I`&8`;P!R`&T`<P!.`&4`90!D`&4`9 `L`" `
M00!C`&,`;P!U`&X`= !2`&4`9@.!E`'(`90!N`&,`90!.`&\`+ `@.`$,`;P!M
M`' `80!N`'D`4@.!E`&<`:0!S`'0`<@.!A`'0`:0!O`&X`3@.!O`"P`( !3`'4`
M< !P`&P`:0!E`'(`3@.!O`"P`( !6`&4`;@.!T`'4`<@.!E`$0`90!B`'0`;P!R
M`$X`;P`L`" `4 !A`'D`;0!E`&X`= !4`&4`<@.!M`',`+ `@.`%,`= !A`'0`
M90!M`&4`;@.!T`$$`9 !D`'(`90!S`',`+ `@.`$4`=@.!E`&X`= !.`&\`= !E
M`',`+ `@.`$0`:0!R`&4`8P!T`&D`;P!N`',`+ `@.`$T`80!P`% `80!T`&@.`
M+ `@.`%,`;P!R`'0`1P!R`&\`=0!P`"P`( !3`&\`<@.!T`"P`( !$`&$`= !E
M`$,`<@.!E`&$`= !E`&0`+ `@.`$0`80!T`&4`30!O`&0`:0!F`&D`90!D`"P`
M( !#`'(`90!A`'0`90!D`$(`>0`L`" `30!O`&0`:0!F`&D`90!D`$(`>0`L
M`" `=0!P`',`:0!Z`&4`7P!T`',`( `G`" `)@.`@.`" `#0`*`" `( `@.`" `
M( `@.`" `( `@.`" `( `@.`" `)P!&`%(`3P!-`" `9 !B`&\`+@.!#`&P`:0!E
M`&X`= !S`" `)P`@.`"8`#0`*`" `( `@.`" `( `@.`" `( `@.`" `( `@.`" `
M)P!7`$@.`10!2`$4`( `H`$,`;P!M`' `80!N`'D`5 !Y`' `90`@.`&P`:0!K
M`&4`( ! `$,`;P!M`' `80!N`'D`5 !Y`' `90`@.`"8`( `B`"4`(@.`I`" `
M00!.`$0`( `G`" `)@.`-``H`( `@.`" `( `@.`" `( `@.`" `( `@.`" `( `@.
M`" `( `@.`" `( `G`"@.`4P!T`&$`= !U`',`( !L`&D`:P!E`" `0 !3`'0`
M80!T`'4`<P`@.`"8`( `B`"4`(@.`I`" `00!.`$0`( `G`" `)@.`-``H`"0`)
M`" `( `@.`" `( `@.`" `( `@.`" `( `G`"@.`20!$`" `20!.`" `* !3`&4`
M; !E`&,`= `@.`$,`;P!M`' `80!N`'D`20!$`" `9@.!R`&\`;0`@.`$,`;P!M
M`' `80!N`'D`0@.!U`',`:0!N`&4`<P!S`$0`:0!V`&D`<P!I`&\`; @.`@.`%<`
M2 !%`%(`10`@.`$(`=0!S`&D`;@.!E`',`<P!$`&D`=@.!I`',`:0!O `&X`/0!
M`$(`=0!S`&D`;@.!E`',`<P!$`&D`=@.!I`',`:0!O`&X`*0`I` "<`#0`*``T`
D"@.!%`%@.`10!#`" `* ! `',`<0!L`"D`#0`*`&<`;P`-``H`
`
end
|||There is no & string operator in T-SQL.
Change to + and your proc "might" work
You don't have to use exec to do this, you can do:
if @.param is null
select ...
else
select ...
Although I would suggest (in order to keep the benefits of stored
procedures:
if @.param is null
exec procedureName
else
exec procedureName @.param = @.param
Then you write two other procedures. The if block with optionally executed
queries can cause poor plans, but having single procedures like this can
really improve performance.
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:exulEIGpFHA.2720@.TK2MSFTNGP10.phx.gbl...
> Ok, I have this sp (attached). Still can't make it work.
> Thanks
> Regards
>
> "vuht2000" <vuht2000@.yahoo.com> wrote in message
> news:1124415729.139947.214020@.g44g2000cwa.googlegr oups.com...
>
>