Multiple Results From One Stored Procedure

May 17, 2007

If I have multiple selects statements in one stored procedure. How do I access the different results in a report in Reporting Services? Where the first select is the detail lines of my report and the second select is just a field for a my header? Or am I going about this wrong with putting it all the results I want into one stored procedure for one report?



Example stored procedure:



ALTER PROCEDURE [dbo].[proc_DepositsByOfficer]

As

SELECT MASTER_DSC.APP

, MASTER_DSC.BRANCH

, qlkpOfficer.strName

, MASTER_DSC.DSC_OFFICER_49

, qlkpBranchName.strDescrS

, MASTER_DSC.DSC_CUR_BAL_21

, Case MASTER_DSC.APP WHEN 1 Then DSC_CUR_BAL_21 End AS DDA_BAL

, Case MASTER_DSC.APP WHEN 2 Then DSC_CUR_BAL_21 End AS SAV_BAL

, Case MASTER_DSC.APP WHEN 3 Then DSC_CUR_BAL_21 End AS CD_BAL

, MASTER_DSC.DSC_INT_RATE_45

, Case When [DSC_CUR_BAL_21]>0 Then ([DSC_CUR_BAL_21]*[DSC_INT_RATE_45])/100 Else 0 End As ANN_EXP

, Case When [DSC_CUR_BAL_21]>0 And MASTER_DSC.APP=1 Then ([DSC_CUR_BAL_21]*[DSC_INT_RATE_45])/100 Else 0 End AS ANN_EXP_DDA

, Case When [DSC_CUR_BAL_21]>0 And MASTER_DSC.APP=2 Then ([DSC_CUR_BAL_21]*[DSC_INT_RATE_45])/100 Else 0 End AS ANN_EXP_SAV

, Case When [DSC_CUR_BAL_21]>0 And MASTER_DSC.APP=3 Then ([DSC_CUR_BAL_21]*[DSC_INT_RATE_45])/100 Else 0 End AS ANN_EXP_CD

, Case MASTER_DSC.APP WHEN 1 Then 1 End AS DDA_Count

, Case MASTER_DSC.APP WHEN 2 Then 1 End AS SAV_Count

, Case MASTER_DSC.APP WHEN 3 Then 1 End AS CD_Count

, qlkpApplicationCode.strDescrS AS strApplCode

FROM

MASTER_DSC

INNER JOIN qlkpApplicationCode ON MASTER_DSC.APP=qlkpApplicationCode.dblReference

LEFT JOIN qlkpOfficer ON MASTER_DSC.DSC_OFFICER_49=qlkpOfficer.intID

LEFT JOIN qlkpBranchName ON MASTER_DSC.BRANCH=qlkpBranchName.dblReference

WHERE

MASTER_DSC.DSC_CUR_BAL_21<>0

ORDER BY

MASTER_DSC.BRANCH;



SELECT dbo.fBankName() AS BankName;

View 7 Replies


ADVERTISEMENT

Please Help To Assign Multiple Results Into Individual Variables, Stored Procedure

Apr 12, 2008

I have a MSSQL2000 table called partspec.dbo.locationIn this table are 2 smallint columns: Tester (numbered 1-40) and Line (numbered with various integers)I am trying to create a stored procedure to read the tester number like so:Select Tester from partspec.dbo.location where Line = 2which will return up to 5 tester number results, lets say 11, 12, 24, 29 ,34My question is how do I store these tester numbers into 5 variables so that I may use them later in the sp ? So it would go something like this:CREATE PROCEDURE Table_Line
(@Tester1       integer,@Tester2        integer,@Tester3    integer,@Tester4    integer,@Tester5    integer)ASSELECT Tester FROM partspec.dbo.location where Line = 2Now this is where I'm confused on how to get 1 value into 1 variable and so on for all 5 values returned. This is what I would like to happen:
@Tester1 = 11@Tester2 = 12@Tester3 = 24@Tester4 = 29@Tester5 = 34GOThank you for any and all assistance.

View 2 Replies View Related

Identifying Results Sets When Stored Procedure Called Multiple Times

Oct 18, 2005

I have a report based on our product names that consists of two parts.Both insert data into a temporary table.1. A single grouped set of results based on all products2. Multiple tables based on individual product names.I am getting data by calling the same stored procedure multipletimes... for the single set of data I use "product like '%'"To get the data for individual products, I am using a cursor to parsethe product list.It's working great except that I have no idea how to identify theresults short of including a column with the product name. While thatis fine, I'm wondering if there is something that is like a header ortitle that I could insert prior to generating the data that would looka little tighter.Thanks in advance-DanielleJoin Bytes!

View 3 Replies View Related

T-SQL (SS2K8) :: Nested Stored Procedure - Multiple Results Of Record Sets Coming

Oct 1, 2014

I am calling stored procedure called GetCommonItemCount within another stored procedure called CheckBoxAvailability, the first stored procedure should return a count to second stored procedure and based on that some logic will be executed.

I have 2 problems in that

1. The result is not coming from first stored so the variable called @Cnt is always 0 although it should be 18
2. At the end i need to see in the output the result from second stored procedure only while now i am seeing multiple results of record sets coming.

I have attached the scripts also, the line i described in step1 is

View 9 Replies View Related

Integration Services :: Assign Variables To Multiple Table Results From Stored Procedure

Sep 21, 2015

If I have a stored procedure that returns 15 tables, how do I distinguish the tables to assign variables to each table in c#?

View 6 Replies View Related

Using A Stored Procedure To Query Other Stored Procedures And Then Return The Results

Jun 13, 2007

Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB  multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID,    S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName',    T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID    FROM [Item].ItemDetails I    INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID    INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID    FROM [Item].ItemDetails IOr something like that... Any thoughts? 

View 3 Replies View Related

How To Stored A Stored Procedure's Results Into A Table

Jul 23, 2005

Hi, How can I store a stored procedure's results(returning dataset) intoa table?Bob*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Need To Know How To Get Stored Procedure Results Into VB

Jun 18, 2004

datagrid. Sorry, I'm new to it. For the few times I've done datagrids in the past, I've built
my own selects and filled the grid. This time I need to use someone elses stored procedure.
I simply don't know how to get the results back into my VB datagrid. Can someone point
me in the right direction? Thanks.

View 6 Replies View Related

Results Of A Stored Procedure

Feb 1, 2007

How do I filter the results of a stored procedure?

View 2 Replies View Related

T-Sql Help - BCP Results Of Stored Procedure

Feb 25, 2008

Hi

I am trying to execute a stored procedure (which returns multiple tables) and use these results to populate an Excel file. I am totally lost on how to capture the results and use it. Any help will be appreciated.

Thanks

View 8 Replies View Related

Stored Procedure Not Returning Results

May 15, 2007

Hi,I'm creating a stored procedure that pulls information from 4 tables based on 1 parameter. This should be very straightforward, but for some reason it doesn't work.Given below are the relevant tables:  SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_Project](
[ProjID] [varchar](300) NOT NULL,
[ProjType] [varchar](20) NULL,
[ProjectTitle] [varchar](max) NULL,
[ProjectDetails] [varchar](max) NULL,
[ProjectManagerID] [int] NULL,
[RequestedBy] [varchar](max) NULL,
[DateRequested] [datetime] NULL,
[DueDate] [datetime] NULL,
[ProjectStatusID] [int] NULL,
CONSTRAINT [PK__tbl_Project__0B91BA14] PRIMARY KEY CLUSTERED
(
[ProjID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_Project] WITH CHECK ADD CONSTRAINT [FK_tbl_Project_tbl_ProjectManager] FOREIGN KEY([ProjectManagerID])
REFERENCES [dbo].[tbl_ProjectManager] ([ProjectManagerID])
GO
ALTER TABLE [dbo].[tbl_Project] CHECK CONSTRAINT [FK_tbl_Project_tbl_ProjectManager]
GO
ALTER TABLE [dbo].[tbl_Project] WITH CHECK ADD CONSTRAINT [FK_tbl_Project_tbl_ProjectStatus] FOREIGN KEY([ProjectStatusID])
REFERENCES [dbo].[tbl_ProjectStatus] ([ProjectStatusID])
GO
ALTER TABLE [dbo].[tbl_Project] CHECK CONSTRAINT [FK_tbl_Project_tbl_ProjectStatus]


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


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_Report](
[ReportName] [varchar](50) NOT NULL,
[ProjID] [varchar](300) NULL,
[DeptCode] [varchar](50) NULL,
[ProjType] [varchar](50) NULL,
[ProjectTitle] [varchar](500) NULL,
[ProjectDetails] [varchar](3000) NULL,
[ProjectManagerID] [int] NULL,
[RequestedBy] [varchar](50) NULL,
[DateRequested] [datetime] NULL,
[DueDate] [datetime] NULL,
[ProjectStatusID] [int] NULL,
CONSTRAINT [PK_tbl_Report] PRIMARY KEY CLUSTERED
(
[ReportName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_Report] WITH CHECK ADD CONSTRAINT [FK_tbl_Report_tbl_ProjectManager] FOREIGN KEY([ProjectManagerID])
REFERENCES [dbo].[tbl_ProjectManager] ([ProjectManagerID])
GO
ALTER TABLE [dbo].[tbl_Report] CHECK CONSTRAINT [FK_tbl_Report_tbl_ProjectManager]
GO
ALTER TABLE [dbo].[tbl_Report] WITH CHECK ADD CONSTRAINT [FK_tbl_Report_tbl_ProjectStatus] FOREIGN KEY([ProjectStatusID])
REFERENCES [dbo].[tbl_ProjectStatus] ([ProjectStatusID])
GO
ALTER TABLE [dbo].[tbl_Report] CHECK CONSTRAINT [FK_tbl_Report_tbl_ProjectStatus]


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


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_ProjectStatus](
[ProjectStatusID] [int] IDENTITY(1,1) NOT NULL,
[ProjectStatus] [varchar](max) NULL,
CONSTRAINT [PK__tbl_ProjectStatu__023D5A04] PRIMARY KEY CLUSTERED
(
[ProjectStatusID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


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


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_ProjectManager](
[ProjectManagerID] [int] IDENTITY(1,1) NOT NULL,
[FName] [varchar](50) NULL,
[LName] [varchar](50) NULL,
[Inactive] [int] NULL,
CONSTRAINT [PK__tbl_ProjectManag__7D78A4E7] PRIMARY KEY CLUSTERED
(
[ProjectManagerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

 And here is the stored procedure that I wrote (doesn't return results):  SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetReportQuery]
(
@ReportName varchar(100)
)

AS

BEGIN

SET
NOCOUNT ON

IF @ReportName IS NULL
BEGIN
RETURN -1
END
ELSE
BEGIN

DECLARE @DeptCode varchar(50), @ProjID varchar(50)
SELECT @DeptCode = DeptCode FROM tbl_Report WHERE ReportName = @ReportName

SET @ProjID = @DeptCode + '-' + '%'

SELECT P.ProjID, P.ProjType, P.ProjectTitle, P.ProjectDetails, M.FName, M.LName, P.DateRequested, P.DueDate, S.ProjectStatus
FROM tbl_Project P, tbl_ProjectManager M, tbl_ProjectStatus S
WHERE ((P.ProjID = (SELECT ProjID FROM tbl_Report WHERE ((ReportName = @ReportName))))
AND (P.ProjectDetails = (SELECT ProjectDetails FROM tbl_Report WHERE ReportName = @ReportName) OR P.ProjectDetails IS NULL)
AND (M.FName = (SELECT FName FROM tbl_ProjectManager WHERE (ProjectManagerID = (SELECT ProjectManagerID FROM tbl_Report WHERE ReportName = @ReportName))) OR M.FName IS NULL)
AND (M.LName = (SELECT LName FROM tbl_ProjectManager WHERE (ProjectManagerID = (SELECT ProjectManagerID FROM tbl_Report WHERE ReportName = @ReportName))) OR M.LName IS NULL)
AND (P.DateRequested = (SELECT DateRequested FROM tbl_Report WHERE ReportName = @ReportName) OR P.DateRequested IS NULL)
AND (P.DueDate = (SELECT DueDate FROM tbl_Report WHERE ReportName = @ReportName) OR P.DueDate IS NULL)
AND (S.ProjectStatus = (SELECT ProjectStatusID FROM tbl_Report WHERE ReportName = @ReportName) OR S.ProjectStatus IS NULL)
)
END

END Can someone see what's wrong? Thanks. 

View 7 Replies View Related

Results From Stored Procedure Inset

Mar 30, 2008

I am having trouble getting data back from my stored procedure insert commands.  Here is what I currently have set up.
1 - A dataset called YagDag
2 - A Table adapter called tadUsers
3 - A Stored Procedure that the tadUsers uses to insert called spUser_i
It seems like the way I have my VB code set up I can only retrieve a return int. I can't figure out how to get my User GUID back, any help would be really appreciated
  Public Shared Function AddUser(ByVal EMail As String, ByVal FirstName As String, ByVal LastName As String, ByVal Password As String, ByVal EMailActive As Boolean) As Integer
Dim strEMail = AppFunction.SQLSafeString(EMail)
Dim strFirstName = AppFunction.SQLSafeString(FirstName)
Dim strLastName = AppFunction.SQLSafeString(LastName)
Dim strPassword = AppFunction.SQLSafeString(Password)
Dim blnEMailActive = EMailActive

Dim Users As New YagDagTableAdapters.tadUsers

Dim guidUserYID As Guid = Users.Insert(strFirstName, strLastName, strPassword)

Return 1
End Function -- =============================================
-- Author:Greg Moser
-- Create date: 3/29/2008
-- Description:Adds a User to the tblUser
-- =============================================
ALTER PROCEDURE [dbo].[spUser_i]
@FirstName varchar(50),
@LastName varchar(50),
@Password varchar(16)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @UserYID uniqueidentifier
SET @UserYID = NEWID()

-- Add User to tblUsers
INSERT INTO tblUsers (
YID,
Password,
FirstName,
LastName
)
VALUES (
@UserYID,
@Password,
@FirstName,
@LastName
)

--Return Users YID
SELECT @UserYID
END 

View 2 Replies View Related

Results From More Than Stored Procedure In A Report

Sep 23, 2004

Hi,
Is there a way I can display the data returned from more than one stored procedure in a report using reporting services. Or can I use more than one dataset in the report????

View 3 Replies View Related

Return Results Set From Stored Procedure

Oct 8, 2004

Two questions - one slightly off topic:

1) How do I write a stored procedure in MS SQL so that it returns a results set?

2) How do I get this into ASP.NET so that I can put the values into controls?

Jags

View 1 Replies View Related

Stored Procedure Not Generating Results

Oct 28, 1999

I wrote a stored procedure with three variables to be passed as input parameters. The stored procedure runs a select into statement into a temp table. The resulting temp table with another table was queried using right outer join to produce the desired results.
The stored procedure compiles error free.However when I ran the stored procedure with the parameters(3) in ISQL/W (SQL Server 6.5) the headers(column-names) were displayed but no records.
When runned as a query with the same parameter values, records were produced.
Help please urgent.

Regards
Olutimi Ooruntoba

View 2 Replies View Related

Inconsistent Stored Procedure Results

Jul 13, 2006

I'm testing some code to look up values from my database and update a specific field when certain conditions are met. I'm having trouble with some code that is giving me the results I expect when I submit one set of parameters, but is not finding anything in the database for another set, when I know the data exists.

Here's the code for my stored procedure, SP:

Code:

@flightid bigint,
@departuretime smalldatetime
SELECT flightid, flightno, departuretime, origincode, destinationcode
FROM flightschedules

WHERE flightid <> @flightid
AND departuretime = CONVERT(SMALLDATETIME, @departuretime, 120)



And here's the vbscript that calls it:


Code:

vOutboundID = 452
vReturnID = 453

'--- Get the flight details ---
strOrigin = "confirmflightdetails '" & vOutboundID & "';"
set rsOrigin = Server.CreateObject("ADODB.Recordset")
rsOrigin.Open strOrigin, objConn
response.write "origin " & rsOrigin("departuretime") & "<BR>"

strReturn = "confirmflightdetails '" & vReturnID & "';"
set rsReturn = Server.CreateObject("ADODB.Recordset")
rsReturn.Open strReturn, objConn
response.write "return " & rsReturn("departuretime") & "<BR>"

strGetOFID = "SP '" & vOutboundID & "', '" & rsOrigin("departuretime") & "';"
set rsOFID = Server.CreateObject("ADODB.Recordset")
rsOFID.Open strGetOFID, objConn

DO WHILE NOT rsOFID.EOF
response.write "OFNO " & rsOFID("flightno") & " " & rsOFID("flightid") & "<br>"
rsOFID.MoveNext
Loop

strGetRFID = "SP '" & vReturnID & "', '" & rsReturn("departuretime") & "';"
set rsRFID = Server.CreateObject("ADODB.Recordset")
rsRFID.Open strGetRFID, objConn

DO WHILE NOT rsRFID.EOF
response.write "RFNO " & rsRFID("flightno") & " " & rsRFID("flightid") & "<br>"
rsRFID.MoveNext
Loop




Here's the code for confirmflightdetails:

Code:

@flightid bigint
AS
SELECT flightid, flightno, departuretime
FROM flightschedules
WHERE flightid = @flightid



When confirmflightdetails is tested, I the proper results, as confirmed by the response.write statements:

4521092006-07-29 08:00:00
4531102006-07-29 12:05:00


I put the response.write statements and loops in so I could verify the functionality.

Here's what it produces:

out 452
ret 453
origin 7/29/2006 8:00:00 AM
return 7/29/2006 12:05:00 PM
OFNO 109 450

Here's what it should produce:

out 452
ret 453
origin 7/29/2006 8:00:00 AM
return 7/29/2006 12:05:00 PM
OFNO 109 450
RFNO 110 451

If I do this in query analyzer:

Code:

select flightid, flightno, departuretime
from flightschedules
where flightid > 449 and flightid < 454



this is what I get from the database:

flightid flightno departuretime origin destination
4521092006-07-29 08:00:00 A C
4501092006-07-29 08:00:00 A B
4531102006-07-29 12:05:00 C A
4511102006-07-29 13:15:00 B A

What I'm trying to do is look up the chosen flight, then find the flight with the matching origin/destination (the other flight leg) on the same day.

I can't figure out why it's working for one set of parameters and not for the other.

Thanks in advance for any help!

View 1 Replies View Related

Using Results From Stored Procedure In WHERE Condition

Dec 19, 2004

Hi,

I'm trying to write a nested stored procedure, with the outer proc named "spAssociate", and inner proc named "spSales".

So far I have created the inner proc

CREATE PROCEDURE spSales@userID intASSET NOCOUNT ON
SELECT SalesOppID
FROM Sales_Opportunities
WHERE SalesOppCurrentStatus NOT IN ('Sale Lost','Sales Closed','Sale Closed','Unqualified','Deferred','Dropped')
AND OppOwnerUserID = @userIDGO

This was successfully created. I wanted to use the return set in the outer proc, which I tried creating as:

CREATE PROCEDURE spAssociate(@userID int,
@containerType varchar(100),
@associateType varchar(100))AS SET NOCOUNT ON
SELECT AssociateID
FROM AppRelations
WHERE ContainerType=@containerType
AND ContainerID IN (EXECUTE spSales @userID)
AND AssociateType=@associateType GO

I get an error "incorrect syntax near execute".

How can I use the results from the inner proc for the WHERE condition in my outer proc?

Any help is greatly appreciated. Thanks!

View 2 Replies View Related

Query Results In Stored Procedure

Jul 31, 2007

how can i refer to the results of a query inside a stored procedure?

and use them later

View 8 Replies View Related

Using Select Results In A Stored Procedure

Jul 23, 2005

I'm sure this is an obvious question but much of SQL is new to me.I have a stored procedure and I want to use the results of a selectstatement elsewhere in the stored prcedure. The select statement willreturn at most one record and only one column, here's an example :select top 1 Sequence from MyTablewhere ParentId=4 and Sequence > 200 order by sequenceCan I put this result in a variable? Do I have to use SELECT INTOTempTable and refer to TempTable and then drop the table at the end ofthe stored procedure? I have read a little about cursors, are thesethe way to go?I'm confused as to what my options are, any help or links to help willbe appreciated.Thanks for reading.

View 1 Replies View Related

Get Results From VB SQLCRL Stored Procedure

Jun 7, 2006

Hi,I created a VB SQL CRL Stored procedure for calculating a value. Valueis returned as belowUsing sConn4 As New SqlConnection("context connection=true")sConn4.Open()scmd = New SqlCommand("SELECT " & var_max, sConn4)sdrd = scmd.ExecuteReader()SqlContext.Pipe.Send(sdrd)End UsingWhen calling this stored procedure from a TSQL stored procedure forusing the value for further processing the value returned to myvariable is 0. The correct value should be 56. In results tab I get thecorrect result, but how can I assign it to my variable @max ?DECLARE @max1 intDECLARE @max intEXEC @max1 = [dbo].[VBSTP_calculate_MAX_no]@vsp_table_name = N'[dbo].[Message]',@vsp_table_key = N'message_no',@vsp_WHERE = N''print @max1 -- value here is 0SET @max = (SELECT @max1)Thanks a lot.

View 1 Replies View Related

Unexpected Results Of Stored Procedure

Jul 8, 2006

hi, hvae a little trouble with thos procedure.

When executed it always adds 2 to the value of fields Telefon and Mobil.

Can anyone help me, please

Kurt



ALTER Procedure opdaterbruger

(

@Initialer nvarchar(100),

@Mailadr nvarchar(100),

@Telefon float(8),

@PlusNet float(8),

@Mobil float(8),

@pnmobil float(8)

)

AS

UPDATE Brugere

SET Mailadresse = @Mailadr

WHERE Initialer = @Initialer

IF (@Initialer= '0') OR NOT EXISTS ( SELECT * FROM PNetlokal WHERE Bruger = @Initialer)

INSERT INTO pnetlokal ( Bruger, Kortnr, Telnr )

VALUES ( @initialer, @Plusnet, @Telefon)

ELSE

UPDATE PNetLokal

SET TelNr = @Telefon, KortNr = @PlusNet

WHERE Bruger = @Initialer

IF (@Initialer= '0') OR NOT EXISTS (SELECT * FROM PlusNet WHERE Bruger = @Initialer )

INSERT INTO plusnet (Bruger, Kortnr, Mobilnr)

VALUES (@initialer, @pnMobil, @mobil)

ELSE

UPDATE PlusNet

SET Mobilnr = @Mobil, KortNr = @pnmobil

WHERE Bruger = @Initialer

View 5 Replies View Related

Return Results Of Stored Procedure

Sep 30, 2006

I need to return the results of a stored procedure into a temporary table. Something like this:

Select * into #temp from exec (the stored procedure). It appears that I can not do this.

The following will not work for me cause I am not sure how many columns will be returned. I want this to work even if the calling stored procedure is changed (i.e add or take away columns)

insert into (...) exec (the stored procedure.

Does anyone have any ideas how I could do this.



View 4 Replies View Related

Multiple Insert Into Multiple Tables With A Stored Procedure

Mar 1, 2007

Hello
I am building a survey application.
 I have 8 questions. 
 Textbox -  Call reference
 Dropdownmenu  - choose Support method
 Radio button lists - Customer satisfaction questions 1-5
Multiline textbox - other comments.
I want to insert textbox, dropdown menu into a db table, then insert each question score into a score column with each question having an ID.
I envisage to do this I will need an insert query for the textbox and dropdownlist and then an insert for each question based on ID and score.
Please help me!
Thanks
Andrew
 

View 9 Replies View Related

Can't Get Sqldatasource To Return Results From A Stored Procedure

Aug 1, 2006

I thought I would impliment a new feature of my web page using stored procedures and the SqlDataSource object, for practice or whatever, since I don't normally use that stuff.
This is the stored procedure:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:Lance Colton
-- Create date: 7/31/06
-- Description:Find the contest winner
-- =============================================

ALTER PROCEDURE [dbo].[AppcheckContest]
-- Add the parameters for the stored procedure here

@BeginDate datetime = '1/1/2006',
@EndDate datetime = '12/31/2006',
@SectionID int = 10,
@WinnerID int = 0 OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT top 1 @WinnerID = P.UserID
FROM cs_Posts P
WHERE P.PostDate BETWEEN @BeginDate AND @EndDate
AND P.SectionID = @SectionID
AND P.UserID <> 2100 -- I don't want to win my own contest...

AND SettingsID = 1000 -- This number could be different if i had more than one CS installed?

AND IsApproved = 1
ORDER BY NEWID() -- yes this is slow, but it works...

RETURN @WinnerID
END
 It's really simple - just needs to return the one randomly chosen integer userID. I've tested it in query designer or whatever it's called in Management Studio and it works fine there at least.
Thinking I was done the hard part, I created a new web form in visual studio, dropped a SqlDataSource on it, and used the 'configure data source' wizard from the smart tag to do all the work for me. I didn't have any trouble using the wizard to select my stored procedure, and i'm using the sa connection string to simplify my debugging. I tried using the FormParameter / FormField way of getting the output and setting the input parameters. I can't seem to get it working though. There's no errors or anything, just the output isn't coming through.
Here's the code from the aspx codebehind file:Partial Class Contest
Inherits System.Web.UI.Page

Protected Sub btnSelectWinner_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSelectWinner.Click
Dim stuff As New System.Web.UI.DataSourceSelectArguments
SqlDataSource1.Select(stuff)
SqlDataSource1.DataBind()
lblWinnerID.Text = SqlDataSource1.SelectParameters("WinnerID").ToString
End Sub
End Class
 As you can see, I wasn't sure if you're supposed to call databind() or select() to actually get the stored procedure to execute, so I tried both. I was hoping the last line of code there would set the label to the value contained in the @WinnerID parameter, but instead it sets it to "WinnerID".
Here's the code from the .aspx file. Most of this was generated by the Wizard, but I messed around with it a bit. <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="Contest.aspx.vb" Inherits="Contest" title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="CPHMain" Runat="Server">
<asp:Button ID="btnSelectWinner" runat="server" Text="Find Winner" />
<asp:Calendar ID="Calendar_From" runat="server"></asp:Calendar>
<asp:Calendar ID="Calendar_To" runat="server"></asp:Calendar>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:appcheck-csConnectionString-sa %>"
SelectCommand="AppcheckContest" SelectCommandType="StoredProcedure" CancelSelectOnNullParameter="False">
<SelectParameters>
<asp:FormParameter FormField="Calendar_From" Name="BeginDate" Type="DateTime" />
<asp:FormParameter FormField="Calendar_To" Name="EndDate" Type="DateTime" />
<asp:Parameter DefaultValue="10" Name="SectionID" Type="Int32" />
<asp:formParameter FormField="lblWinnerID" defaultvalue="666" Direction="InputOutput" Name="WinnerID" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
 
<asp:Label ID="lblWinnerID" runat="server" Text="???"></asp:Label>
</asp:Content> 

View 3 Replies View Related

How To Put A Select Statement And Get Results In A Stored Procedure

Feb 2, 2008

Hello,I have written a stored procedure where I prompt the user for the Year and the Month (see below)How do I take the variable @TheMonth and find out how many days is in the month and then loop to display a total for every day in the selected month.Can someone please point me in the right direction.
CREATE PROCEDURE crm_contact_frequency_report
@TheYear         varchar(4),@TheMonth      varchar(2)
AS
SELECT   
/* EMAILS (B) */(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN Email B ON A.subject = B.subject  WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)  AND   (B.directioncode = 1)) AS    Total_EmailOutgoing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN Email B ON A.subject = B.subject  WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)  AND   (B.directioncode = 0)) AS    Total_EmailImconing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN Email B ON A.subject = B.subject  WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)  AND   (B.directioncode IS NULL)) AS    Total_EmailNotListed,
(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN Email B ON A.subject = B.subject  WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)) AS    Total_All_Emails,
/* PHONE CALLS (C) */(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN PhoneCall C ON A.subject = C.subject  WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)  AND   (C.directioncode = 1)) AS    Total_CallOutgoing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN PhoneCall C ON A.subject = C.subject  WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)  AND   (C.directioncode = 0)) AS    Total_CallIncoming,
(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN PhoneCall C ON A.subject = C.subject  WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)  AND   (C.directioncode IS NULL)) AS    Total_CallNotListed,
(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN PhoneCall C ON A.subject = C.subject  WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)) AS    Total_All_Calls,
/* FAXES (D) */(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN Fax D ON A.subject = D.subject  WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)  AND   (D.directioncode = 1)) AS    Total_FaxOutgoing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN Fax D ON A.subject = D.subject  WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)  AND   (D.directioncode = 0)) AS    Total_FaxIncoming,
(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN Fax D ON A.subject = D.subject  WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)  AND   (D.directioncode IS NULL)) AS    Total_FaxNotListed,
(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN Fax D ON A.subject = D.subject  WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)) AS    Total_All_Faxes
FROM   CampaignResponse AGO

View 2 Replies View Related

How Do I Access The Results Of A Query In The Stored Procedure Itself ??

Apr 7, 2008

Hi I've run into a slight problem with my stored procedure.If I fire a query in a stored procedure, how can I access the results of that query in the stored procedure itself ?? ALTER PROCEDURE GetDetailsOfWebsiteUserForMail    @request_id intAS    declare @k int    select person_id from Website_user w, Request r    where r.user_id=w.user_id and r.request_id=@request_id        set @k =person_id /* What do I do here??, value returned is unique */           if (@k!=0)  /* Exists */        begin         /*  do something */        end            else /* entry on person table does not exist */        begin          /*  do something */
        end     

View 1 Replies View Related

Accessing SELECT Results Within A Stored Procedure

May 18, 2004

Hi,
Can anyone tell me how to access the results from a select statement within the stored procedure?

This is to implement auditting functionality, before updating a record i want select the original record and compare the contents of each field with the varibles that are passed in, if the fields contents has changed i want to write an audit entry.

So i'd like to store the results of the select statement in a variable and access it like a dataset. e.g

declare selectResult

set selectResult = Select field1,field2,field3 from table1

if selectResult.field1 <> @field1
begin
exec writeAudit @var1,@var2,var3
end


Many thanks.

View 4 Replies View Related

SELECT And Stored Procedure Producing Different Results?!?!?

Aug 29, 2005

I ran my SP in QA and it brought back some data from my live database but I had my test DB selected from the DDL at the top of the screen.  So i ran a Select statement on the whole table in a different query window (with the same parameters)and it gave me the correct info.  Any idea y?  They both should be bringing back information from the same table not one from my live DB and one from my TEST DB.  Please help me--I'm so lost!min max rate <----This is what my stored procedure brought back(data from my live DB) ---it has parameters 90 100 .4080 89 .3070 79 .2559 69 .1549 58 .10The stored procedure is as follows:  CREATE PROCEDURE dbo.sp_WageMatrix_GetRate( @CompanyID nvarchar(2), @FacilityID nvarchar(2))AS SET NOCOUNT ON;SELECT [Min], [Max], Rate FROM Performance.dbo.WageMatrix WHERE (CompanyID = @CompanyID) AND (FacilityID = @FacilityID) AND PeriodID = dbo.fn_GetCurrentPeriod(CompanyID, FacilityID)ORDER BY RangeIDmy select statement brought back the following:min max rate 90 100 .4080 89 .3070 79 .2560 69 .15and I ran the following select statement:  SELECT [Min], [Max], [Rate] FROM [PerformanceDEV].[dbo].[WageMatrix] where companyid = '21' and facilityid = '01' and periodid = 2order by rangeid

View 2 Replies View Related

Exporting Stored Procedure Results To Csv File

Oct 1, 2007

Hi all,

Please ignore my ignorance, but I was wondering if someone could help me out a bit?

I'm trying to export the results from a stored proc, to an csv file.

So for example, I want to run this query:

SELECT TOP 10 pkID FROM myTable ORDER BY pkID DESC

and I wanted it to be saved to:

C:spExport.csv

How would I go about doing that?

Hope you can help :-)


bwuk

View 2 Replies View Related

Stored Procedure/Query Results To PDF Or Excel

May 2, 2007

I'm looking for a tool that can schedule stored procedures or queries and save the results to pdf or excel (preferrably both). SQL Server 2000, so 2005 reporting services are out, though if that has the capability I'd like to know because that might convince some of the powers that be to upgrade.

View 4 Replies View Related

Use Select Staement Results In A Stored Procedure

Jan 10, 2006

Hi.

I am new to SQl server. I would like to write a query with the follwing logic:
---------
Select department from table1;

then insert each result row into a table with in the same query.
---------

View 2 Replies View Related

SELECT Stored Procedure Results Into Variable

Aug 21, 2007

Is there a way to select the results of an SP into a variable.

For example:
DECLARE @X varchar(1000)
SELECT @X= sp_who2

How do I do this?

View 3 Replies View Related

Output A File With Results Of Stored Procedure

Feb 3, 2008

I have a stored procedure that outputs an XML result. As it is right now, I will periodically go into SQL Server Mngmnt Studio and run the procedure, then save the XML output to an XML file that my website uses. The procedure takes too long run (it is recursive through the entire data set) to have the website call the procedure on its own, and wait for the results.

My question is this: Can I create a trigger that will run this stored procedure and save the results to a file? Is that possible? The trigger would call the procedure any time a specific table is updated (which will be a rare occurrence), then save the file to the path provided so the website users could always have the most up-to-date information without having long wait times caused by long waits for the procedure to run.

Any advice on how best to accomplish this will be appreciated.
Thanks in advance.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved