Returning Multiple Values From A Stored Procedure.
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 Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
SELECT Returning Multiple Values In A Stored Proc
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 Replies !
View Related
Returning Values From Stored Procedure
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 Replies !
View Related
Returning Parameter Values From Stored Procedure
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 Replies !
View Related
Returning Multiple Rows From A Stored Procedure
Hi,I have the following stored procedure that does some processing andputs the result in a temporary table. I tried several things thatprocedure to display output that I can access with ADO.Net, but itdoesn't work. It doesn't even display the result in the query analyzerunless I add SELECT @ReturnFullNameAny help?The stored procedure:CREATE PROCEDURE sp_SEARCH_MULTIPLE_NAMES @search4fatherOfvarchar(255), @maximum_fathers int = 100, @ReturnFullName varchar(255)Output....SELECT @ReturnFullName = name FROM #FULLNAME------------------------------------------------To Execute the stored procedure:DECLARE @test varchar(255)EXEC sp_SEARCH_MULTIPLE_NAMES @search4fatherof='مريم',@returnfullname=@testPRINT CONVERT(varchar(255), @test)
View Replies !
View Related
Stored Procedure Returning Multiple Rows
I have a stored procedure which return a single value and one which return multiple rows between two colums. In my code for the procedure which returns a single value i use (executescalar) which works fine. I am not sure what command to use in my code when i am calling the stored procedure that returns multiple rows between colums. Any help would be appreciated. Thanks.
View Replies !
View Related
Returning And Reading Multiple Select Statements From One Stored Procedure
Hey Guys. I’m having a little trouble and was wondering if you could help me out. I’m trying to create a custom paging control, so I create a stored procedure that returns the appropriate records as well as the total amount of records. And that works fine. What I’m having problems with is reading the data from the second select statement within the code. Anyone have any idea on how to do this? Also.. how can I check how many tables were returned? Here's my code. I'm trying to keep it very generic so I can send it any sql statement:public DataTable connect(string sql) { DataTable dt = new DataTable(); SqlConnection SqlCon = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["MyDB"].ToString()); SqlDataAdapter SqlCmd = new SqlDataAdapter(sql, SqlCon); System.Data.DataSet ds = new System.Data.DataSet(); SqlCmd.Fill(ds); dt = ds.Tables[0]; //Here's where I don't know how to access the second select statement return dt; } Here's my stored procedure: ALTER PROCEDURE dbo.MyStoredProcedure ( @Page int, @AmountPerPage int, @TotalRecords int output ) AS WITH MyTable AS ( Select *, ROW_NUMBER() OVER(ORDER BY ID Desc) as RowNum From Table where Deleted <> 1 ) select * from MyTable WHERE RowNum > (((@Page-1)*@AmountPerPage)) and RowNum < ((@Page*@AmountPerPage)+1); Select @TotalRecords = COUNT(*) from Table where Deleted <> 1 RETURN Thanks
View Replies !
View Related
Handling Multiple Values From A Stored Procedure In ASP.NET.
Hi all, I’m returning two values from a stored procedure, one is a basic string confirming that an email has been sent and the other is the normal value returned from running an INSERT statement. So in my code I’m using the ExecuteNonQuery() method. I’m not sure how to handle both returned values in my code in my data layer. This is what I have: ExecuteNonQuery(cmd); return Convert.ToString(cmd.Parameters["@ReturnedValue"].Value).ToLower(); Obviously I’d need to return the value returned by the ExecuteNonQuery method as well, normally I’d simply convert the value to an int and precede this with the return keyword like so: return (int)ExecuteNonQuery(cmd); Obviously I can’t do this as I need to return two values, the normal value returned by the ExecuteNonQuery() method and my own output parameter value. Any ideas how I can do both? My current method containing the code further above returns a string but clearly this doesn’t help. I’m guessing that maybe I should return an object array so I can return both values? I haven’t encountered this problem before so I’m just guessing. Please help. Thanks
View Replies !
View Related
Stored Procedure Returning 2 Result Sets - How Do I Stop The Procedure From Returning The First?
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 Replies !
View Related
Passing Multiple Values From A Listbox Into A Stored Procedure
hi i have a listbox with selectedmode = multiple, i am currently using this code in my code behind (c#) to call the storedprocedure within the datasource but its not working: Do i have to write specific code in c# to send the mulitple values through?protected void confButton_Click(object sender, EventArgs e) { try {foreach (ListItem item in authorsListBox4.Items) {if (item.Selected) { AddConfSqlDataSource.Insert(); } }saveStatusLabel.Text = "Save Successfull: The above publication has been saved"; }catch (Exception ex) {saveStatusLabel.Text = "Save Failed: The above publication failed to save" + ex.Message; } }
View Replies !
View Related
How To Pass Multiple Values To An &"IN&" Clause Through Stored Procedure
I created a stored procedure like the following in the hope that I can pass mulitple company_id to the select statement: CREATE PROC sp_test @in_company_code nvarchar(1024) AS select company_code, name, description from member_company where company_code in (@in_company_code) However, I tried the following : exec sp_test 'abc', 'rrd', 'bbc' Procedure or function sp_test has too many arguments specified. and SQLServer doesn't like it. Did I specify this stored procedure correct? If so, how can I can pass multiple values to the stored procedure then to the sql statement? If not, is it possible to specify a stored procedure like this? Thanks!
View Replies !
View Related
Problem Returning Two Values From Stored Procedures
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 Replies !
View Related
Returning Value To Web App Via Stored Procedure
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 Replies !
View Related
Stored Procedure Not Returning Value
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 Replies !
View Related
How To Get The Returning Value Of A Stored Procedure?
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 Replies !
View Related
Stored Procedure Not Returning Value
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 Replies !
View Related
Stored Procedure - Returning A Value
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 Replies !
View Related
Returning Id From SQL Stored Procedure
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 Replies !
View Related
Returning A Value From A Stored Procedure
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 Replies !
View Related
Returning Value From SQL Stored Procedure
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 Replies !
View Related
Returning All Rows For A Stored Procedure
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 Replies !
View Related
Stored Procedure Not Returning Results
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 Replies !
View Related
Stored Procedure Returning No Rows
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 Replies !
View Related
Stored Procedure Not Returning Recordset In ASP
Hi All. My question is this. I have a complex stored procedure in SQLServer which works fine when I run it in Query Analyzer. However, whenI call it within my ASP script, it returns nothing, and sometimes locksup. If I change my script to call other existing stored procedures itworks fine. It's just with this particular stored proc. I have triedvarious DB calls in ASP, such as opening the recordset through an ADOconnection and through the Command object but to no avail. Here is mySQL:SET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS OFFGOALTER PROCEDURE dbo.sp_getProfessionalsByTypeID@typeID intASdeclare @catID intdeclare @userID intdeclare @strUserName varchar(100)declare @strFirstName varchar(100)declare @strLastName varchar(100)declare @strTypeName varchar(100)declare @strCategoryName varchar(100)declare @strPictureURL varchar(100)declare @sql varchar(1000)declare @a varchar(100)declare @b varchar(100)declare @c varchar(100)declare @d varchar(100)beginset @a=''set @b=''set @c=''set @d=''--Create Temp TableCREATE TABLE #QueryResult (nCatID int, nTypeID int, nUserID int,strUserName varchar(100), strFirstName varchar(100), strLastNamevarchar(100), strTypeName varchar(100), strCategoryNamevarchar(100),strPictureURL varchar(100))--Search QuerybeginINSERT #QueryResultSELECTdbo.tbl_musician_type.nCatID, dbo.tbl_musician_type.nTypeID,dbo.tbl_users.nUserID, dbo.tbl_users.strUserName,dbo.tbl_users.strLastName,dbo.tbl_users.strFirstName,dbo.tbl_musician_type.strTypeName, dbo.tbl_category.strCategoryName,dbo.tbl_professionals.strPictureURLFROMdbo.tbl_musician_type INNER JOINdbo.tbl_category ON dbo.tbl_musician_type.nCatID= dbo.tbl_category.nCategoryID INNER JOINdbo.tbl_profile ONdbo.tbl_musician_type.nTypeID = dbo.tbl_profile.nTypeID INNER JOINdbo.tbl_users ON dbo.tbl_profile.nUserID =dbo.tbl_users.nUserID LEFT OUTER JOINdbo.tbl_professionals ON dbo.tbl_users.nUserID= dbo.tbl_professionals.nUserIDWHEREdbo.tbl_musician_type.nTypeID = @typeIDend--Create Temp TableCREATE TABLE #QueryResult2 (ID int IDENTITY,nCatID int, nTypeID int,nUserID int, strUserName varchar(100), strFirstName varchar(100),strLastName varchar(100), strTypeName varchar(100), strCategoryNamevarchar(100),strPictureURL varchar(100), strArtist varchar(100),strAlbumTitle varchar(100), strRecordLabel varchar(100), strYearvarchar(100))--Now Declare the Cursor for Speakersdeclare cur_musicians CURSOR FOR--Combined Results Groupedselect distinct nCatID, nTypeID, nUserID, strUserName, strLastName,strFirstName, strTypeName, strCategoryName, strPictureURLFrom #QueryResultopen cur_musiciansfetch next from cur_musicians INTO @catID, @typeID, @userID,@strUserName, @strLastName, @strFirstName, @strTypeName,@strCategoryName, @strPictureURL--Loop Through Cursorwhile @@FETCH_STATUS = 0beginSELECT TOP 1 @a = strArtist, @b=strAlbumTitle,@c=strRecordLabel, @d=strYearFROM dbo.tbl_profile_discogwhere nTypeID = @typeID AND nCategoryID = @catID AND nUserID =@userIDinsert #QueryResult2select @catID as nCatID, @typeID as nTypeID, @userID as nUserID,@strUserName as strUserName, @strLastName as strLastName, @strFirstNamestrFirstName, @strTypeName as strTypeName, @strCategoryName asstrCategoryName, @strPictureURL as strPictureURL, @a ashighlightArtist, @b as highlightAlbumTitle, @c as highlightRecordLabel,@d as highlightYearfetch next from cur_musicians INTO @catID, @typeID, @userID,@strUserName, @strLastName, @strFirstName, @strTypeName,@strCategoryName, @strPictureURLset @a = ''set @b=''set @c=''set @d=''endselect * from #QueryResult2 TI--Clean Upclose cur_musiciansdeallocate cur_musiciansdrop table #QueryResultdrop table #QueryResult2endGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO
View Replies !
View Related
SQL Stored Procedure Not Returning Result Set
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 Replies !
View Related
Stored Procedure Returning Too Few Rows
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 Replies !
View Related
Recordset Returning In Stored Procedure
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 Replies !
View Related
Stored Procedure Not Returning Recordset From ADO
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 Replies !
View Related
Returning More Than One Result Set From A Stored Procedure
Hi, I am wondering wether it is possible to return more than one result set to my application from a stored procedure. My sp is below, but all that returns to my application is the result of the first iteration of the invoice_cursor. Any help or alternate methods to achieve this would be appreciated. Code: CREATE PROCEDURE procBillingSummaryCastingDollars( @startDate Varchar(10),@endDate Varchar(10)) AS DECLARE @invoice_id varchar(11); DECLARE invoice_cursor CURSOR FOR SELECT invoice_id FROM receivable_dist rd WHERE rd.POSTING_DATE BETWEEN @startDate AND @endDate AND (rd.GL_ACCOUNT_ID like '%3000%' OR rd.GL_ACCOUNT_ID like '%3101%') order by invoice_id OPEN invoice_cursor FETCH NEXT FROM invoice_cursor INTO @invoice_id WHILE @@FETCH_STATUS = 0 BEGIN -- Declare an inner cursor based -- on invoice_id from the outer cursor. IF charindex('C',@invoice_id) = 0 AND charindex('M',@invoice_id) = 0 BEGIN SELECT ss.metal_code, ss.equip_code, ss.core, CASE r.status WHEN 'X' THEN rl.amount*-1 ELSE rl.amount END as CastingDollars FROM receivable r INNER JOIN receivable_line rl ON r.invoice_id = rl.invoice_id INNER JOIN cust_order_line cl ON cl.cust_order_id = rl.cust_order_id INNER JOIN salesum_partid ss ON ss.partid = cl.part_id WHERE rl.invoice_id = @invoice_id END ELSE BEGIN SELECT ss.metal_code, ss.equip_code, ss.core, CASE r.status WHEN 'X' THEN rl.amount*-1 ELSE rl.amount END as CastingDollars FROM receivable r INNER JOIN receivable_line rl ON r.invoice_id = rl.invoice_id INNER JOIN salesum_partid ss ON substring(ltrim(rl.reference),1,(patindex('%-_-____%',ltrim(rl.reference))+6)) = ss.partid WHERE r.invoice_id = @invoice_id END FETCH NEXT FROM invoice_cursor INTO @invoice_id END CLOSE invoice_cursor DEALLOCATE invoice_cursor GO
View Replies !
View Related
Returning SSIS Stored Procedure Name
Hi I am currently trying to write a number of processe's to keep track of what information is held in my SSIS package. The package I have created is rather large and it would prove a long labourious process to look through every task to see what stored procedure has been used. What I wanted to do was write a stored procedure in SQL Server 2005 that pick's up each package name and checks for any stored procedures used and returns the names of these stored procedures and any other relevant information i.e required variables. So far I have managed to create a stored procedure that picks up the name(s) of the packages but I am stuck after this. Declare @Filename varchar(1000) Declare @cmd varchar(1000) Create table #dir (Filename varchar(1000)) Insert #dir Exec master..xp_cmdshell 'dir /B C:DevelopmentSumColumnSumColumn*.dtsx' delete #dir where Filename is null or Filename like '%not found%' Select @Filename = '' While @Filename < (select max(Filename) from #dir) drop table #dir Any help would be appreciated. Thanks
View Replies !
View Related
Stored Procedure Not Returning Recordset
The stored procedure I created returns all records from the table if no parameters are passed, as expected. When I pass in the only parameter, I get 0 records returned when there should be one or more returned. I'm sure it's something simple in my syntax, but I don't see it. This call returns all records: exec webservices_BENEFICIAL_USES_DM_SELECT This call returns 0 records, when it should return 1: exec webservices_BENEFICIAL_USES_DM_SELECT @DISPOSAL_AREA_NAME='Cell 8' Here is the stored procedure: ALTER PROCEDURE [dbo].[webservices_BENEFICIAL_USES_DM_SELECT] -- Add the parameters for the stored procedure here @DISPOSAL_AREA_NAME DISPOSAL_AREA_NAME_TYPE = NULL AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here IF @DISPOSAL_AREA_NAME IS NULL BEGIN SELECT* FROMBENEFICIAL_USES_DM END ELSE BEGIN SELECT* FROMBENEFICIAL_USES_DM WHEREDISPOSAL_AREA_NAME = '@DISPOSAL_AREA_NAME' END END
View Replies !
View Related
Stored Procedure Apparently Returning DBNull When It Isn't
Hi,I have a stored procedure which returns an integer indicating whether the operation has succeeded or failed. It does this by calling set @returnvalue = (0 or 1) then select @returnvalue as returnvalue in the stored procedure code. When I run the stored procedure, inputting a set of parameters as it expects, it does what it's supposed to do and outputs a table with a single row and a single column, called returnvalue, which has a value of either 1 or 0. When I run the stored procedure through a function in asp, using the exact same set of parameters, output the results as a data set and try and convert the return as an integer I get the following error: "Object Cannot be cast from DBNull from other types"The conversion code is ..return Convert.ToInt32(ds.Tables[0].Rows[0]["returnvalue"]);And if I change "returnvalue" to anything else (like "wibble" for example) then I get a different error telling me it can't find the column named "wibble". So the dataset definitely contains a table with a column in it called returnvalue, as you expect. But it's value is, apparently, DBNull instead of an integer.How come this works when called directly through SQL and fails when called through ASP? Can anyone help? This is really doing my head in!Cheers,Matt
View Replies !
View Related
Problem Returning OUTPUT In Stored Procedure
Hi, I have this output, @RegisterFlag int OUTPUTand I have a transaction going on, so my code (I just put some relevant code here) is: 1 BEGIN TRAN 2 SELECT @getDealername = OrgName FROM Org WHERE OrgName = @DealerName 3 If @getDealername is null 4 5 ELSE 6 BEGIN 7 set @RegisterFlag = 2 8 ROLLBACK TRAN 9 RETURN 10 END 11 12 COMMIT TRAN 13 set @RegisterFlag = 1 The problem I am facing now is I couldn't get @RegisterFlag = 2 return back to my asp.net code when it reached line 7, instead I got this error mesg:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0. How do I fix this? Many big thanks.
View Replies !
View Related
SQL 2005 Stored Procedure Not Returning Output Value To Web App
I am having probelms trying to get a stored proedure to return an output value. The outline of the specific request is that I supply a varchar which is unique within a set of tables (tables named in another table). I want to search each of the tables until I find the one that has the value and when found return the GUID of the row and the table name. I have not been successful in getting the value of the GUID (an int) to be returned. If I run in debug and stop after the SQL call I can see the value in the SQL output parameter but it does not appear in the variable I specified in the SQL parameter setting. I am probably doing something simplly wrong but cannot see it. Please help if you can. Regards, Major (that is my Christian name ;-) The code files are copied below. Web app code sing System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.SqlClient; using EFormsLIB; public partial class _Default : System.Web.UI.Page { String gstrConnectionString; // Connection to the SQL database, set in the config file to allow for changes while testing and running protected void Page_Load(object sender, EventArgs e) { // Read the connection string from the config file EFormsRoutines r = new EFormsRoutines(); gstrConnectionString = r.ReadConfigString("EForms21ConnectionString", ""); } protected void btnCancel_Click(object sender, EventArgs e) { this.Dispose(); } protected void btnGet_Click(object sender, EventArgs e) { // Get GUID for the form using UniqueDocID as the BlueWare ID. int intGUID = -1; SqlConnection SqlCDB = new SqlConnection(gstrConnectionString); SqlCommand SqlCmd = SqlCDB.CreateCommand(); SqlCmd.CommandText = "GetGUIDfromBWID"; SqlCmd.CommandType = CommandType.StoredProcedure; SqlParameter BWIDParameter = new SqlParameter(); BWIDParameter.ParameterName = "@BWID"; BWIDParameter.SqlDbType = SqlDbType.VarChar; BWIDParameter.Size = 30; BWIDParameter.Direction = ParameterDirection.Input; BWIDParameter.Value = TextBox1.Text; SqlCmd.Parameters.Add(BWIDParameter); SqlParameter GUIDParameter = new SqlParameter(); GUIDParameter.ParameterName = "@GUID"; GUIDParameter.SqlDbType = SqlDbType.Int; GUIDParameter.Direction = ParameterDirection.Output; GUIDParameter.Value = intGUID; SqlCmd.Parameters.Add(GUIDParameter); SqlCmd.Connection.Open(); int ret1 = SqlCmd.ExecuteNonQuery(); SqlCmd.Connection.Close(); Label1.Text = intGUID.ToString(); } } web app aspx <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Untitled Page</title> </head> <body> <form id="form1" runat="server"> <div> <asp:Label ID="Label1" runat="server" Style="z-index: 100; left: 48px; position: absolute; top: 24px" Text="Label"></asp:Label> <asp:Button ID="btnGet" runat="server" OnClick="btnGet_Click" Text="Get" style="z-index: 101; left: 8px; position: absolute; top: 48px" /> <asp:Button ID="btnCancel" runat="server" OnClick="btnCancel_Click" Text="Cancel" style="z-index: 102; left: 64px; position: absolute; top: 48px" /> <asp:TextBox ID="TextBox1" runat="server" Style="z-index: 103; left: 48px; position: absolute; top: 0px" Width="48px"></asp:TextBox> <asp:Label ID="Label2" runat="server" Style="z-index: 104; left: 0px; position: absolute; top: 0px" Text="BWID"></asp:Label> <asp:Label ID="Label3" runat="server" Style="z-index: 106; left: 0px; position: absolute; top: 24px" Text="GUID"></asp:Label> </div> </form> </body> </html> SQL procedure set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[GetGUIDfromBWID] @BWID varchar(30), @GUID int outputASBEGIN SET NOCOUNT ON; declare formscursor cursor for select formname from forms for read only declare @found int, @formname varchar(30) select @found = 0 open formscursor fetch next from formscursor into @formname while (@found = 0) begin if (@@fetch_status <> 0) select @found = 2 else begin if @formname = 'UAT040' begin if (select count(*) from UAT040 where BWID = @BWID) =1 begin select @GUID = (select GUID from UAT040 where BWID = @BWID) select @found = 1 end end else if @formname = 'GEN001' begin if (select count(*) from GEN001 where BWID = @BWID) =1 begin select @GUID = (select GUID from GEN001 where BWID = @BWID) select @found = 1 end end else if @formname = 'GEN002' begin if (select count(*) from GEN002 where BWID = @BWID) =1 begin select @GUID = (select GUID from GEN002 where BWID = @BWID) select @found = 1 end end else if @formname = 'CFT001' begin if (select count(*) from CFT001 where BWID = @BWID) =1 begin select @GUID = (select GUID from CFT001 where BWID = @BWID) select @found = 1 end end fetch next from formscursor into @formname end end deallocate formscursor if @found = 2 select @GUID = -16 select @GUIDend
View Replies !
View Related
Statement For Returning Stored Procedure Params
Hi all,I'd like to put together a SQL statement that will take the name of astored procedure as a param, and return that SP's parameters.I'm writing a test application, and I'd like to wrte a generator tosave myself some time, but I can't seem to figure out how to get theparams from a SP. Any help would be appreciated.Thanks in advance,Craig
View Replies !
View Related
Stored Procedure Not Returning Rows In Web Application
Hi, I Have created a stored procedure to use full text search and return the results back, When i run this procedure in the query analyzer it is working fine, but when i run this procedure from asp.net application, it is returning zero rows. I have checked all the parameters and everything in my web application, there is nothing wrong in there. Another stored procedure which almost do the samething with some different parameters is working fine on both ends. I am using Sql server 2005 (express) + VS.NET 2005. and using ASPNET to connect to database. Thanks in advance for any suggestions.
View Replies !
View Related
SQL Stored Procedure Not Returning Expected Records.
I am running a SP using this code: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SelectQueryAddressPostCodeSearchOnly] ( @postcode nvarchar(50) ) AS SET NOCOUNT ON; SELECT Cust_Address.Cust_Address_ID, Cust_Address.Cust_Address_1, Cust_Address.Cust_Address_2, Cust_Address.Cust_Address_Post_Code, Cust_Address.Cust_Post_Town, Post_Town.Post_Town_ID, Post_Town.Post_Town_Name, Post_Town.Post_Town_Priority FROM Cust_Address INNER JOIN Post_Town ON Cust_Address.Cust_Post_Town = Post_Town.Post_Town_ID WHERE Cust_Address.Cust_Post_Town LIKE @postcode The value for @postcode being passed for testing is "%SA%". I would expect this to return all records which have "SA" in the Post Code field. In the test database I am using there are several (Mainly SA43 0EZ). However the SP in fact returns no matching records at all. Am I not understanding something here, or is there something to do with space in post code field that is causing a problem ? I have other SPs that are behaving just fine. Cheers Matt
View Replies !
View Related
Stored Procedure - Returning Data Using Parameters
Hello, I am trying to get records related to a Device_ID with the following conditions: IF LastModified_Date BETWEEN (@p_datetime_StartDate AND @p_datetime_EndDate) OUTPUT ELSE OUTPUT ALL. I want records between the range of date selected, or all the records if no date entered. Can somebody help me ASAP. Here is what I have so far. I just don't know where to put the IF...THEN ELSE. SELECT Device_ID, Action, Username, LastModified_Date, FROM ActivityMonitorActionLogs WHERE Device_ID = ISNULL (@p_int_Device_ID, AL.Device_ID) AND LastModified_Date BETWEEN (@p_datetime_StartDate AND @p_datetime_EndDate) Thanks a lot!!! Mylene
View Replies !
View Related
|
TRACKER
