Returning Parameter Values From Stored Procedure

Oct 16, 2001

I am having trouble using Output parameters. I have set up an Execute SQL Task to call a Stored Procedure. I am passing an input parameter and indicating 1 global variable for output to retrieve a unique value from the stored procedure call. When I execute the step, it completes successfully but nothing is returned in my output parameter for the unique value??? Below is the call that I use:

EXEC SYS_GENERATE_ID ?

-in the Execute SQL Task I click the Parameters button to set up the input:
Status = Parameter1
and the Output Variable Type:
Rowset = GUID.

the stored procedure:
CREATE PROCEDURE dbo.SYS_GENERATE_ID
(@Statusvarchar(20),
@GUID uniqueidentifier = NULL OUTPUT)
AS
BEGIN
SET @GUID = NEWID()
INSERT INTO dbo.ACTIVITY (GUID, STATUS)
VALUES (@GUID, @Status)
SET @GUID = convert(varchar(50), @GUID)
RETURN @@ERROR
END

A table is correctly populated but the GUID value does not make it back to the calling task??? The Global Variable Type under Package Properties has been changed to "Dispatch" and the value is "Not Displayable"???
Any help that is offered will be appreciated GREATLY!!

View 2 Replies


ADVERTISEMENT

Returning Values From Stored Procedure

Aug 16, 2004

Hi,
How to return values from stored procedures?? I have a value whose variable would be set thru this sp and it should return this value. How to do this?

Thanks,

View 1 Replies View Related

Returning Values From Stored Procedure

Jun 9, 2006

Hi Using Following Stored Procedure,

Which always returns Null,

What s the error,



CREATE PROCEDURE prLoginAuth

(

@pStrUserName varchar(50),
@pStrPassword varchar(50),
@pOutput Varchar(20) Output
)

AS

Declare @V_Facilities Varchar(50)

SELECT Facilities=@V_Facilities From UserLoginFacilities where LoginID=(Select LoginID From UserLogin where LoginName=@pStrUserName and Password=@pStrPassword)

If(@V_Facilities=null)

Set @pOutput = @V_Facilities

Return @pOutput;

Else

Set @pOutput = @V_Facilities

Return @pOutput;

GO



Anyone correct this query , I want return the output from this procedure

Thanx in advance

Selva.R

View 3 Replies View Related

Returning Multiple Values From A Stored Procedure.

Feb 7, 2007

my stored procedure performs actions of deletion and insertion. Both the inserted and deleted items are output in temp tables with single column.
Is there a way to return the content of these two tables?
Is there a way to return a table from the stored procedure?

Thanks in advance
waamax

View 1 Replies View Related

Help With Parameter Values In Stored Procedure

Jun 26, 2007

Hi,
I am using a stored Procedure where I am passing some parameter values.Following is my Code.

CREATE proc Usp_Rpt_GetDetails
@Fromdt varchar(12),
@ToDt varchar(12),
@ApscId numeric,
@StatusCode varchar(1),
@val numeric
as

Begin

if @StatusCode = "C" then @ vall(1,2)

End
select
'' as unuseid,
substring(ltrim(rtrim(s.Spares_Code)),1,12) as Code,
oh.WO_Number AS Claim_Id,
ltrim(rtrim(sc.section_code)) AS section_code,
ltrim(rtrim(dc.defect_code)) AS defect_code,
ltrim(rtrim(at.Action_Taken_Code)) AS Repair_Code,
cs.Call_status_code
from [32_Trans_Work_Order_Spares_Detail] ws
inner join [32_Trans_Work_Order_Header] oh on oh.WO_Number = ws.WO_Number
inner join [11_Master_Spares]s on s.Spares_ID = ws.Spares_ID
inner join [31_Master_Section_Code] sc on sc.Section_ID = ws.Section_Code_ID
inner join [31_Master_Defect_Code] dc on dc.Defect_ID = ws.Defect_Code_ID
inner join [10_Master_Equipment_Status] e on e.Equipment_Status_ID = oh.Equipment_Status_ID
inner join [00_Master_Country] c on c.Country_ID = mp.Country_ID
where e.Equipment_Status_ID in (1,2) and cs.Call_Status_ID in (1,2) and oh.WO_Record_Date between @Fromdt and @ToDt
and oh.WO_Status='C'


My Problem is How to pass values to parameters
Status Code Consists of values C, V, R which i am passing from the Front End
along with Call_Status_ID which can be 1,2.

Thanks ...

View 1 Replies View Related

How To Pass Parameter Values To Stored Procedure In Rs

Mar 16, 2007

this is the error  ...

An error has occured during report processing.

Query execution failed for dataset 'dataset name'
Procedure 'procedure name ' expects parameter '@StartDate', which was not supplied

View 3 Replies View Related

Pass Parameter Values To Stored Procedure In Dataset

Jul 10, 2007

I have a stored procedure "spDetailsByDay" which takes parameters @StartDateTime as datetime, @Day as int, @Hour as int, @Value1 as varchar(20), @value2 as varchar(20)



My report Parameters are StartDateTime as DateTime, Day as integer, Hour as integer, Value1 as string, Value2 as string, ReportType as string

In the dataset, I typed

=IIF(Parameters!ReportType.Value="Day", "EXEC spDetailsByDay " & Parameters!StartDateTime.Value & "," & Parameters!Day.Value & "," & Parameters!Hour.Value & "," & Parameters!Value1.Value & "," & Parameters!Value2.Value", "EXEC spDetailsByMonth")



I am getting syntax errors. Can anyone help me how to pass parameters to stored procedure in dataset.



Thanks.

View 4 Replies View Related

Stored Procedure Returning 2 Result Sets - How Do I Stop The Procedure From Returning The First?

Jan 10, 2007

I hvae a stored procedure that has this at the end of it:
BEGIN
      EXEC @ActionID = ActionInsert '', @PackageID, @AnotherID, 0, ''
END
SET NOCOUNT OFF
 
SELECT Something
FROM Something
Joins…..
Where Something = Something
now, ActionInsert returns a Value, and has a SELECT @ActionID at the end of the stored procedure.
What's happening, if that 2nd line that I pasted gets called, 2 result sets are being returned. How can I modify this SToredProcedure to stop returning the result set from ActionINsert?

View 2 Replies View Related

Pass Multi-values In A Single Parameter For Stored Procedure

Apr 16, 2013

I got issue when passing multiple values to a single parameter. Here is my stored procedure as following:

CREATE PROCEDURE [dbo].[School]
@Grade AS varchar(50),
@Class As varchar(50)
AS
BEGIN
SELECT Name, Grade, Class
FROM School
WHERE Grade = (IsNull(@Grade, Grade)) AND Class IN (IsNull(@Class, Class ))
END

In the front end, I got multiple values for Subject parameters such as Math, English, Reading, etc... in a specified class. How do I can modify my above stored procedure to receive multiple values for a single parameter.

View 14 Replies View Related

Passing Parameter Values To Full Text Search Stored Procedure

Oct 24, 2006

I thought this would be quite simple but can't find the correct syntax:ALTER Procedure usp_Product_Search_Artist_ProductTitle

(@ProductTitle varchar(255))

AS

SELECT ProductTitle

FROM tbl_Product


WHERE CONTAINS(dbo.tbl_Product.ProductTitle, @ProductTitle)
   My problem is that if I pass "LCD Flat Screen" as teh @ProductTitle parameter the query falls over as it contains spaces. Guess i'm looking for something like: WHERE CONTAINS(dbo.tbl_Product.ProductTitle, '"' + @ProductTitle + "'")Thanks in advance.R  

View 4 Replies View Related

Returning Values When Null Parameter Selected

Apr 3, 2008

i have a stored procedure that has parameters. this dataset is used in a crystal report. the parameters are in the sp and will return records if a value is selected. i would like to return records if one parameter or all parameters are selected.
there are 3 parameters, date range, receipt, po #
if i select a distinct value and leave the others null, or 2 values 1 null etc, i would like to return the records, i am having trouble with the syntax. thank you

View 1 Replies View Related

Integration Services :: Pass Multiple Parameter Values To SSIS Package In 2012 Through Stored Procedure?

Jul 9, 2015

we can  assign one parameter value for each excecution of  [SSISDB].[catalog].[set_object_parameter_value] by calling this catalog procedure..

Example: If I have 5 parameters in SSIS package ,to assign a value to those 5 parameters at run time should I call this [SSISDB].[catalog].[set_object_parameter_value] procedure 5 times ? or is there a way we can pass all the 5 parameters at 1 time .

1. Wondering if there is a way to pass multiple parameters in a single execution (for instance to pass XML string values ??)
2.What are the options to pass multiple parameter values to ssis package through stored procedure.?

View 4 Replies View Related

Returning All Selected Values In Multi-valued Parameter

Feb 15, 2007

How does one return all selected values in a multi-valued parameter? Right now i have a filter on the dataset where

(Expression)

=Fields!LOCATION_ID.Value

(Operator)

=

(Value)

=Parameters!Loc.Value(0)

This is just giving me data from the first value that is selected in the multi-valued dropdown. I need all returned from the parameter. Any ideas.

View 4 Replies View Related

Problem Returning Two Values From Stored Procedures

Jan 29, 2004

Hi, i am trying to return two values from SQL 2000 using a single stored procedure. The stored working fine in Query Analyser and returns the two values and two grids in the results window.

My problem is that when i execute the stored procedure using ADO.Net the dataset only has one of the values. e.g TId : 2, where it should read 'TId' : 2, 'ConfigPath': 'C:lah'

Please could anyone shed ligth on this problem?

here the code for the stored procedure:

CREATE PROCEDURE dbo.GetTillInfo
(
@TillIdR varchar(50),
@Password varchar(50)
)
AS

declare @TillId int
declare @configpath varchar(150)

IF Exists (SELECT Id FROM Tills WHERE TillRef=@TillId and TillPassword=@Password)
BEGIN

set @TillIdR = (SELECT Id FROM Tills WHERE TillRef=@TillId and TillPassword=@Password)
select @TillIdR as 'TId'

set @configpath = (SELECT configpath from customer,tills where
tills.customerid = customer.id and tills.id = @login)
select @configpath as 'ConfigPath'
END
ELSE
BEGIN
set @TillIdR = 0
select @TillIdR as 'TId'
set @configpath =''
select @configpath as 'ConfigPath'
END
GO

View 1 Replies View Related

SELECT Returning Multiple Values In A Stored Proc

Jul 20, 2005

HiI'm not sure what the best approach for this is:I have a stored procedure which I would like to use to return severaloutput values instead of returning a recordset.CREATE PROCEDURE Test (@param1 int, @param2 int OUTPUT, @param3 intOUTPUT) ASSELECT field2, field3 FROM Table WHERE field1 = @param1I would like to return @param2 as field2 and @param3 as field3How do I do this without using SELECT multiple times?THanks in advanceSam

View 6 Replies View Related

SQL Server 2014 :: Embed Parameter In Name Of Stored Procedure Called From Within Another Stored Procedure?

Jan 29, 2015

I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?

CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],

[Code] ....

View 9 Replies View Related

Returning Value To Web App Via Stored Procedure

Feb 14, 2007

I am trying to run an update stored procedure that will add 1 revision to the rev field and return the Value back to my Application. My number is incrementing by 2 and not 1.
Here is my Stored Procedure
CREATE PROCEDURE dbo.sp_Update_file
@kbid big,@filename nvarchar(50),@rev big OUTPUT,@moddate datetime,@owner nvarchar(50),@author nvarchar(50)AsUPDATE KBFile
SET rev = rev + 1,filename = @filename,moddate = @moddate,owner = @owner,author = @author,@rev = (Select rev from kbfile where kbid = @kbid)WHERE kbid = @kbIDGO

View 3 Replies View Related

Stored Procedure Not Returning Value

May 31, 2005

Ok, still a little bit novice on stored procedures, and can't see why this one doesn't return the value I need.In this case, lessonLocation.CREATE  PROCEDURE dbo.getLocation @studentId     VARCHAR(20), @lessonLocation   VARCHAR(50)  OUTPUTASBEGIN DECLARE @errCode     INT
  SELECT lessonLocation  FROM   cmiDataModel  WHERE  studentId = @studentId
 SET @errCode = 0  RETURN @errCode RETURN @lessonLocation
HANDLE_APPERR:  SET @errCode = 1 RETURN @errCodeHANDLE_DBERR:
 SET @errCode = -1 RETURN @errCodeENDGOIn the query analyzer, it returns the value in the db, but always blank when run in the .Net app.Thanks all,Zath

View 5 Replies View Related

How To Get The Returning Value Of A Stored Procedure?

Feb 22, 2006

Hi everyone!
I am new to sql server 2005 and visual studio 2005.

I have the following simple stored procedure that checks if a user exists:
-------------------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[sp_Users_AlreadyExists]
   
    @UserName varchar(256)
AS
BEGIN
    SET NOCOUNT ON;

   
    IF (EXISTS (SELECT UserName FROM dbo.Users WHERE LOWER(@UserName) = LoweredUserName ))
        RETURN(1)
    ELSE
        RETURN(0)
END
-------------------------------------------------------------------------------------------------------

I use the following code to execute the procedure on visual studio:
-------------------------------------------------------------------------------------------------------
.
.
.
cmdobj As SqlCommand
cmdobj = New SqlCommand(sp_Users_AlreadyExists, connobj)
cmdobj.CommandType = CommandType.StoredProcedure
cmdobj.Parameters.AddWithValue("@UserName", "blablalala")
cmdobj.ExecuteNonQuery()
cmdobj.Dispose()
connobj.Close()
.
.
.
-------------------------------------------------------------------------------------------------------

I expected that cmdobj.ExecuteNonQuery() would return 1 if the user
blablab exists or 0 if the user doesnt, but it just return -1 (i think
because no row was affected)

Does anyone knows how to retrieve the value that my stored procedure returns?

Thanx in advance!

View 1 Replies View Related

Stored Procedure Not Returning Value

Mar 14, 2006

Trying to get a count on records that match search and all I'm getting is 0. I'm using the same basic sp and code elsewhere and it works fine. Anyone see anything wrong here?
Stored Procedure:CREATE PROCEDURE GetResultsCount(@searchCatalog nVarchar(100))ASRETURN ( SELECT COUNT(*) FROM CatalogWHERE itemName LIKE @searchCatalog ORitemLongDesc LIKE @searchCatalog)GO
Code:        Dim connStr As SqlConnection        Dim cmdResultsCount As SqlCommand        Dim paramReturnCount As SqlParameter        Dim intResultsCount As Integer        connStr = New SqlConnection(ConfigurationSettings.AppSettings("sqlCon.ConnectionString"))        cmdResultsCount = New SqlCommand("GetResultsCount", connStr)        cmdResultsCount.CommandType = CommandType.StoredProcedure        cmdResultsCount.Parameters.Add("@searchCatalog", Request.QueryString("search"))        paramReturnCount = cmdResultsCount.Parameters.Add("ReturnValue", SqlDbType.Int)        paramReturnCount.Direction = ParameterDirection.ReturnValue        connStr.Open()        cmdResultsCount.ExecuteNonQuery()        intResultsCount = cmdResultsCount.Parameters("ReturnValue").Value        connStr.Close()        Me.lblResultsCount.Text = intResultsCount

View 8 Replies View Related

Returning Id From SQL Stored Procedure

Dec 6, 2004

Im creating an app in VB.NET that calls a stored procedure which inserts data into one of my tables. I need it to return the id it creates (autonumber) to the VB.NET Program

I keep raising an exception in VB.NET...I want to make sure my stored procedure is correct first...sadly this is my first procedure ive created.

CREATE PROCEDURE db_addtocontractInsert
@contractid int,
@playerid int,
@numyears smallint,
@year1 float(8),
@year2 float(8),
@year3 float(8),
@year4 float(8),
@year5 float(8),
@yearsremain smallint
AS
INSERT INTO db_Contracts
VALUES (@playerid,@numyears,@year1,@year2,@year3,@year4,@year5,@yearsremain)

SELECT @contractid
Return @contractid
GO

-----------------------------------------------
Any tips or corrections that need to be made would be greatly appreciated.

Thanks

Tainter

View 1 Replies View Related

Stored Procedure Not Returning A Value

Jan 10, 2015

If I pass in the value of 1, the stored procedure should return the value of (.5360268), else it should return 0 for any other value(null/blank/empty/etc)

The following stored procedure is returning the value 0 if I pass in the value of 1 which is wrong. And I would need to set to 0 for any other value. I am checking only for null or empty, but the if condition should check any value (except 1) and return 0 for it.

ALTER PROCEDURE [dbo].[Calculator]
(
@ExtraCardiacArteriopathy bit
)
AS
BEGIN
declare @zarterio decimal(14,10)

[Code] .....

View 6 Replies View Related

Returning Value From SQL Stored Procedure

Sep 29, 2005

Hi Guys, I have a series of count statements in a Stored Procedure that return some values. What I want to do is take all of the values that have been returned by the Count statements, add them up and return the value to my .Net Code, but for some reason whenever I try I carry on getting a returned value of 0.

I have checked that the statements are in fact returning values other than 0, and I was wondering if there is something that I am doing wrong in the code below:

<code>
CREATE PROCEDURE [dbo].[UCOutstanding]
@userid int
AS
DECLARE @Num as Int
DECLARE @Num2 as Int
DECLARE @Num3 as Int
DECLARE @Num4 as Int

SET @Num = (SELECT count(*) FROM images, albums, memorial WHERE (images.active=0 AND images.albumid=albums.id AND albums.memorialid=memorial.id AND memorial.userid=@userid))
SET @Num2 = (SELECT count(*) FROM downloads, memorial WHERE (downloads.active=0 AND downloads.memorialid=memorial.id AND memorial.userid=@userid))
SET @Num3 = (SELECT count(*) FROM images, comments, albums, memorial WHERE (comments.active=0 AND comments.imageid=images.id AND images.albumid=albums.id AND albums.memorialid=memorial.id AND memorial.userid=@userid))
SET @Num4 = (SELECT count(*) FROM memorial, story WHERE (story.memorialID=memorial.id and memorial.userid=@userid))


DECLARE @Total as Int
SET @Total = @Num + @Num2 + @Num3 + @Num4

Return @Total
GO
</code>

Thanks for the Help
GP

View 5 Replies View Related

Returning A Value From A Stored Procedure

Jan 22, 2008

I have a Sproc shown below I want the procedure to return the New_ID value back to the calling program how can I do this.
The current approach is not working. Please help.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
.................................
ALTER PROCEDURE [dbo].[SP]
(
@Id int=NULL ,
@site_id int = null,
@Date datetime = null,
)
AS
BEGIN
Declare @New_ID varchar(10)
EXEC [dbo].[SP_GENERATEID] @New_ID output
INSERT INTO A_Table(
CODE,INT_ID,SITE_INT_ID,DATE,
)
Values
(
@New_ID,@Id,@site_id,@Date )
return @New_ID
END
Thanks

View 3 Replies View Related

Stored Procedure - Returning A Value

Jul 20, 2005

Hi All.Maybe someone in here could help on this too....Uusally I can return a value from a stored procedure without any problem.Today I ran into something I cannot figure out.Basically....what I am doing is a couple of inserts or updates depending onwhat is being passed.So in the storedproc tag, I am passing the necessary values along with 1output param. I was using an INOUT param, but I figured I would play itsafe since it wasn't working.So...in the stored procedure, I do some conditions inside transactionstatements...I don't have the code with me right now as I am home, but I figure if I cangive you the general idea, you may know what the problem is.So I have something like this...BEGIN TRANIF team_ID is nullBEGINIF target_ID > 0BEGIN--- INSERT processingSET @OutPutVariable = Scope_Identity()ENDIF target_ID < 0BEGIN--- INSERT processingSET @OutPutVariable = Scope_Identity()ENDENDIF team_ID is not nullBEGIN-- UPDATE ProcessingENDCOMMIT TRANSELECT @OutPutVariableIf I run this procedure through enterprise, i get what I need....the valueof the last inserted record. When I do it through CF, I always get 0 ORnothing at all.If I do a SELECT 100, I get a return value of 100 of course, so it seemslike it's out of scope.Any ideas?

View 5 Replies View Related

Stored Procedure Returning No Rows

Mar 23, 2007

I have a stored procedure below that returns a table of coaches.  It worked before now it does not.  It returns nothing, in vistual studio 2005 and on my asp.net webpage.  But when I execute it in query analyzer with just SELECT * FROM Coaches it returns the rows.  There is no error just will not return what I need.  I recreated the database and stored procedure still doing the same thing.  Any ideas?  Would this be on my server hosting side? ALTER PROCEDURE [dbo].[GetCo]ASSELECT * FROM Coaches  

View 2 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

Returning All Rows For A Stored Procedure

Mar 16, 2004

Hi,

I created a temporary table inside a stored procedure called TmpCursor and the last time I include this..

Select * from #TmpSummary
GO

Inside my web page, I have the following code...

QrySummary = "Exec TmpCursor"
Set rsSummary = Server.CreateObject("ADODB.RecordSet")
rsSummary.Open QrySummary, cnCentral

cnCentral is my sqlconnection string..


This is the error I got when viewing the page

Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.

BTW, the stored procedure works fine in Query Analyzer.

TIA

View 2 Replies View Related

Returning A Sqlstring From A Stored Procedure

Jun 6, 2005

Anyone know how to return a sql command from a sql server stored procedure using asp.net c# and sql server 2000?I'm trying to debug the stored proc and thought the easiest way would be to return the insert command and debug it in the query analyzer.Thanks.Doug.

View 2 Replies View Related

Stored Procedure Not Returning Recordset From ADO

Jul 12, 2000

HELP HELP HELP!!

I have two questions.

I'm using VB 6.0 with ADO to SQL 7.0. My stored procedure works fine with Query Analyzer. I pass one parameter.

sp_test "1234"

And I get a recordset returned.

The stored procedure looks like this:

CREATE PROCEDURE sp_test
@facility_key varchar(255) = null
AS
/*DECLARE @sql1 varchar(255)*/

SELECT * FROM v_reimbursement_report
WHERE facility_key = @facility_key

/*IF @facility_key <> null
PRINT "0"
BEGIN
SELECT * FROM v_reimbursement_report
WHERE facility_key = @facility_key
END*/

When I uncomment the commented lines, I can still get a recordset returned using the query analyzer. And I get the same recordset returned when I use VB ADO and leave the stored proc lines commented out. However, when I call the procedure using the same VB code with the stored proc lines uncommented, I get -1 returned for rs.recordcount:

Private Sub Main_Click()
On Error GoTo Err_Main

Dim lsFacility As String
Dim lsReportName As String
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim gconn As ADODB.Connection
Dim param_facility_key As ADODB.Parameter

Dim gConnString As String

gConnString = "Trusted_Connection=Yes;UID=sa;PWD=yoyo;DATABASE=y ada;SERVER=ya;Network=dbnmpntw;Address=ya;DRIVER={ SQL Server};DSN=''"
Set gconn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
gconn.Open gConnString

cmd.Parameters.Refresh
lsFacility = "1234"

Set param_facility_key = cmd.CreateParameter("facility_key", adVarChar, adParamInput, 255)
cmd.Parameters.Append param_facility_key
param_facility_key.Value = lsFacility

cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_reimbursement_report_test"
cmd.Name = "sp_reimbursement_report_test"

Set cmd.ActiveConnection = gconn

rs.Open cmd, , adOpenKeyset, adLockOptimistic

MsgBox rs.RecordCount

Exit_Main:
Screen.MousePointer = vbNormal
gconn.Close
rs.Close
Exit Sub

Err_Main:
Screen.MousePointer = vbNormal
MsgBox "Error " & Err.Number & " has occurred: " & Err.Description


End Sub


Here are my questions:

1. Why don't I get a recordset returned to VB if I have anything (the commented lines) except a simple SELECT statement in the stored proc.

2. Why must I do a Print "0" (or anything) command in the stored procedure within the IF statement to see a recordset return?

TIA for any help you can give....this one has been keeping me up....and my company down.

JWB

View 3 Replies View Related

Recordset Returning In Stored Procedure

Apr 2, 2000

Dear all,
I have a big problem in using SQL Server stored procedures:
When I have two select statement in the same procedure, the first one will use for returning specific information for the second one to select appropiate result to the client, but the stored procedure just return the first select statement recordset! What can I do? (I use VB Data Environment to access the stored procedures)

View 2 Replies View Related

SQL Stored Procedure Not Returning Result Set

May 8, 2006

This is a strange issue I'm encountering. I have a websphere application that calls a basic Stored Procedure in SQL Server 2000. The SP works fine. Now, if I create a TEMP table in the SP, I no longer get a resultset. No results are returned by the websphere. Even if I do not use the temp table... that is, I just create the temp table like so:
SELECT region_code,
OperGroup_Code,
country_name,
MajorGroup_Name,
RptgEntity_Name,
shell_code,
CRC,
' ' as right_type
INTO #tmpTShell FROM TShell WHERE 1=0

But I then grab my records using the query that works (querying another table), I still get NULL for records returned. Only when I comment out the above TEMP table creation do I get the results back in websphere. Another strange thing is that this works fin in SQL analyzer. WRegardless of whether I have the temp table creation commented out or not, it always works in the Query Analyzer whe I call the SP. It just seems to effect WebSphere or my java code in that it returns null if I create the temp table.

Has anyone ever experienced anthying like this? Any help would be greatly appreciated!

Thanks.

View 5 Replies View Related

Stored Procedure Returning Too Few Rows

Feb 18, 2004

I can't even think how I would go about beginning to solve this problem, so any tips/pointers would help immensely.

I'm running a query that builds a time line of a process using messages in a table called Event. The Event table is joined with a table called Charge_Info based on N_Charge, the charge number. It builds the time line by looking in the Message field for various text strings that signify a specific point has been reached in the process and builds a record for each charge number with data from N_Charge and timestamps from the events table.

The problem I have is that there aren't records for every event in the Event table, ie, for every charge that doesn't have a "Heating Complete" event, the returned recordset has no mention of that charge. The system recording these events is pretty much crap, but there's nothing to be done about that right now. What I want to happen is for the query to return every record in N_Charge and a null string or NULL in place of the non-existing events.

The stored procedure is pasted below. I pass the dates I want and select from the Event table into a temporary table and then do the join/text search on that table. It helped performance tremendously compared to the text search searching all 400,000+ records in the Event table and then doing the date select.

Thanks for any help in advance,
TimC

SELECT EVENT.Time_Stamp, EVENT.N_CHARGE, EVENT.Message
INTO #EVENT_FILTERED
FROM EVENT
WHERE (EVENT.TIME_STAMP >= @StartDate) AND (EVENT.TIME_STAMP < @EndDate)

SELECT CHARGE_INFO.TIME_STAMP, CHARGE_INFO.N_CHARGE, CHARGE_INFO.BASE,
CHARGE_INFO.N_RECIPE, CHARGE_INFO.N_FCE, CHARGE_INFO.N_CH,
CHARGE_INFO.HEIGHT, CHARGE_INFO.CREW_EOC, CHARGE_INFO.CREW_SOC,
CHARGE_INFO.TIME_START, CHARGE_INFO.TIME_FCE_SET,
CHARGE_INFO.TIME_FCE_IGNITED, CHARGE_INFO.TIME_FCE_REMOVED,
CHARGE_INFO.TIME_CH_SET, CHARGE_INFO.TIME_CH_REMOVED,
CHARGE_INFO.WEIGHT, CHARGE_INFO.TIME_POST_PRG_COMPLETE,
EVENT.TIME_STAMP AS IC_Set,
EVENT_1.TIME_STAMP AS Cycle_Started,
EVENT_2.TIME_STAMP AS Leak_Test_Done,
EVENT_3.TIME_STAMP AS End_N2_PrePurge,
EVENT_4.TIME_STAMP AS Heating_Complete,
EVENT_5.TIME_STAMP AS Split_Temp_Met,
EVENT_6.TIME_STAMP AS End_N2_Final_Purge,
EVENT_7.TIME_STAMP AS Inner_Cover_Removed,
EVENT_8.TIME_STAMP AS Cycle_Complete,
EVENT_9.TIME_STAMP AS Post_Purge_Time_Met

FROM dbo.CHARGE_INFO CHARGE_INFO LEFT OUTER JOIN
#EVENT_FILTERED EVENT_9 ON CHARGE_INFO.N_CHARGE = EVENT_9.N_CHARGE LEFT OUTER JOIN
#EVENT_FILTERED EVENT_7 ON CHARGE_INFO.N_CHARGE = EVENT_7.N_CHARGE LEFT OUTER JOIN
#EVENT_FILTERED EVENT_6 ON CHARGE_INFO.N_CHARGE = EVENT_6.N_CHARGE LEFT OUTER JOIN
#EVENT_FILTERED EVENT_8 ON CHARGE_INFO.N_CHARGE = EVENT_8.N_CHARGE LEFT OUTER JOIN
#EVENT_FILTERED EVENT_5 ON CHARGE_INFO.N_CHARGE = EVENT_5.N_CHARGE LEFT OUTER JOIN
#EVENT_FILTERED EVENT_3 ON CHARGE_INFO.N_CHARGE = EVENT_3.N_CHARGE LEFT OUTER JOIN
#EVENT_FILTERED EVENT_1 ON CHARGE_INFO.N_CHARGE = EVENT_1.N_CHARGE LEFT OUTER JOIN
#EVENT_FILTERED EVENT_4 ON CHARGE_INFO.N_CHARGE = EVENT_4.N_CHARGE LEFT OUTER JOIN
#EVENT_FILTERED EVENT_2 ON CHARGE_INFO.N_CHARGE = EVENT_2.N_CHARGE LEFT OUTER JOIN
#EVENT_FILTERED EVENT ON CHARGE_INFO.N_CHARGE = EVENT.N_CHARGE

WHERE (EVENT.MESSAGE LIKE '%Inner Cover Set%') AND
(EVENT_1.MESSAGE LIKE '%Cycle Started%') AND
(EVENT_2.MESSAGE LIKE '%Leak Test Done%') AND
(EVENT_3.MESSAGE LIKE '%End of N2 PrePurge%') AND
(EVENT_4.MESSAGE LIKE '%Heating Complete%') AND
(EVENT_5.MESSAGE LIKE '%Split Temp Met%') AND
(EVENT_6.MESSAGE LIKE '%End N2 Final%') AND
(EVENT_7.MESSAGE LIKE '%Inner Cover Removed%') AND
(EVENT_8.MESSAGE LIKE '%Cycle Complete%') AND
(EVENT_9.MESSAGE LIKE '%Post Purge Time Met%') AND
(CHARGE_INFO.TIME_STAMP >= @StartDate) AND
(CHARGE_INFO.TIME_STAMP < @EndDate)

ORDER BY CHARGE_INFO.BASE, CHARGE_INFO.TIME_STAMP DESC

View 2 Replies View Related







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