Select Script Returning Different Results When Used In A Stored Procedure.
Nov 20, 2007
I have a script with a number of different Left Joins. When I run it in Query Analyzer the Left Join works as a normal join and suppresses some rows. However when the same code is used in a stored procedure the correct results are produced when the stored procedure is executed.
Can anyone tell me what is causing this?
SELECT USR.ROLEUSER
,PER.NAME
,EMP.PAYGROUP
,USR.ROLENAME
,OPR.EMPLID
,OPR.OPRDEFNDESC
,OPR.ACCTLOCK
,OPR.ROWSECCLASS
,EMP.JOBCODE
,JCD.DESCR
,EMP.DEPTID
,EMP.DEPTNAME
FROM PSROLEUSER USR
JOIN
PSOPRDEFN OPR
ON
USR.ROLEUSER = OPR.OPRID
LEFT OUTER JOIN
PS_PERSONAL_DATA PER
ON
OPR.EMPLID = PER.EMPLID
LEFT OUTER JOIN
PS_EMPLOYEES EMP
ON
OPR.EMPLID = EMP.EMPLID
LEFT OUTER JOIN
PS_JOBCODE_TBL JCD
ON
JCD.SETID = 'RBLTT'
AND EMP.JOBCODE = JCD.JOBCODE
AND JCD.EFFDT =
(SELECT MAX(EFFDT)
FROM PS_JOBCODE_TBL
WHERE JCD.SETID = SETID
AND JCD.JOBCODE = JOBCODE
AND EFFDT <= GETDATE())
WHERE OPR.ACCTLOCK = 0
AND USR.ROLENAME = 'RBL MANAGER'
AND EMP.PAYGROUP NOT IN ('RBA', 'RMA')
View 2 Replies
ADVERTISEMENT
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
Mar 11, 2015
Firstly may I say that the sproc I am having problems with and the service that calls it is inherited technical debt from an unsupervised contractor. We are not able to go through a rewriting process at the moment so need to live with this if possible.
Background
We have a service written in c# that is processing packages of xml that contain up to 100 elements of goods consignment data. In amongst that element is an identifier for each consignment. This is nvarchar(22) in our table. I have not observed any IDs that are different in length in the XML element.
The service picks up these packages from MSMQ, extracts the data using XPATH and passes the ID into the SPROC in question. This searches for the ID in one of our tables and returns a bool to the service indicating whether it was found or not. If found then we add a new row to another table. If not found then it ignores and continues processing.
Observations
The service seems to be dealing with a top end of around 10 messages a minute... so a max of about 1000 calls to the SPROC per minute. Multi-threading has been used to process these packages but as I am assured, sprocs are threadsafe. It is completing the calls without issue but intermittently it will return FALSE. For these IDs I am observing that they exist on the table mostly (there are the odd exceptions where they are legitimately missing). e.g Yesterday I was watching the logs and on seeing a message saying that an ID had not been found I checked the database and could see that the ID had been entered a day earlier according to an Entered Timestamp.
So the Sproc...
USE [xxxxxxxxxx]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[Code]....
So on occasions (about 0.33% of the time) it is failing to get a bit 1 setting in @bFound after the SELECT TOP(1).
The only suggestions I can make have been...
change @pIdentifier nvarchar(25) to nvarchar(22)
Trim any potential blanks from either side of both parts of the identifier comparison
Change the SELECT TOP(1) to an EXISTS
The only other thought is the two way parameter direction in the C# for the result OUTPUT. Not sure why he did it that way or what the purpose is.
I have been unable to replicate this using a test app and our test databases. Has observed selects failing to find even though the data is there, like this before?
View 6 Replies
View Related
Aug 20, 2013
Is it possible to return the results of a stored procedure into either a CTE or temp table?
In other words, is it possible to do this:
with someCTE as (
exec someStoredProc
)
or this:
exec someStoredProc into #tempTable
???
View 9 Replies
View Related
May 27, 2014
I have stored procedure
ALTER PROCEDURE dbo.usp_Create_Fact_Job (@startDate date, @endDate date) AS
/*--Debug--*/
--DECLARE @startDate date
--DECLARE @endDate date
--SET @startDate = '01 APR 2014'
--SET @endDate = '02 APR 2014'
;
/*-- end of Debug*/
WITH CTE_one AS ( blah blah blah)
SELECT a whole bunch of fields from the joined tables and CTEs...When I run the code inside the stored procedure by Declaring and setting the start and enddates manually the code runs in 4 minutes (missing some indexes ).When I call the stored procedure with the ExEC
DECLARE@return_value int
EXEC@return_value = [ClaimCenter].[usp_Create_Fact_Job]
@startDate = '01 apr 2014',
@endDate = '01 apr 2014'
SELECT'Return Value' = @return_value
It never returns a results set but doesn't error out either. I have left it for 40 minutes and still no joy.The sproc is reasonably complicated; 6 CTEs to find the most recent version of records and some 2 joins to parent tables (parent and grandparent), 3 joins to child tables (child, grandchild and great grandchild) and 3 joins to lookup views each of which self references a table to filter for last version of a record.
View 3 Replies
View Related
Dec 3, 2006
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 3 Replies
View Related
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
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
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
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
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
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
Jul 23, 2005
I need to run a stored procedure on all the results of a select query.For a simplified example, I'd like to be able to do something likethis:SELECT JobID, QtyToAddFROM JobsWHERE QtyToAdd > 0Then for the results of the above:EXEC sp_stockadd @JobID, @QtyToAddWhere the above line ran for every result from the above select query.Anyone able to shed some light on how to do this?Many thanks,James
View 2 Replies
View Related
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
Dec 4, 2014
I have a stored procedure on a SQL Server 2008 database. The stored procedure is very simple, just a SELECT statement. When I run it, it returns 422 rows. However, when I run the SELECT statement from the stored procedure, it returns 467 rows. I've tried this by running both the stored procedure and the SELECT statement in the same SSMS window at the same time, and the behavior is the same. The stored procedure is:
USE [REMS]
GO
/****** Object: StoredProcedure [mobile].[GetAllMobileDeviceUsers] Script Date: 12/04/2014 */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
When I do this in the same SSMS window:
exec mobile.GetAllMobileDeviceUsers
SELECT
ee.EmployeeID,
EmployeeName = LastName + ', ' + FirstName
FROM EmployeeInvData ee
--UNION
[Code] ....
I get two result sets. The first is 422 rows; the second is 467 rows. Why?
View 4 Replies
View Related
Oct 2, 2007
Hi,
Hope you guys won't mind this rather newbie question. I'm writing a simple blog page for my website and have created a SqlDataSource which queries the database for a list of blog post titles (from the web.Blog table) and the number of comments (from the web.BlogComments table). The SqlDataSource control is:
<asp:SqlDataSource ID="sourceBlogArticles" ProviderName="System.Data.SqlClient" connectionString="<%$ ConnectionStrings:myDatabase %>" runat="server" SelectCommand="SELECT gb.blogID, gb.title, gb.description, gb.tags, gb.dateAdded, COUNT(gbc.blogID) AS noOfComments FROM web.Blog gb LEFT OUTER JOIN web.BlogComments gbc ON gb.blogID = gbc.blogID GROUP BY gb.blogID, gb.title, gb.description, gb.tags, gb.dateAdded ORDER BY gb.dateAdded"></asp:SqlDataSource>
This works perfectly well if each blog entry in the web.Blog table has associated comments in the web.BlogComments table. However, if there are no comments yet defined in the web.BlogComments table for that blogID then no row is returned in ASP.Net (as checked with a GridView control or similar linked to the data source to view what I get)
HOWEVER, I think the SELECT command IS correct: if I use the select command as a query in SQL Server Managment Studio Express, I do get the rows returned, with 0 for the number of comments which is what I would expect for that query:
blogID, title, description, tags, dateAdded, noOfComments
1, title 1, description for title 1, tag1, 2007-09-27 06:49:03.810, 32, title 2, description for title 2, tag2, 2007-09-27 06:49:37.513, 03, title 3, description for title3, tag3, 2007-10-02 18:21:30.467, 0
Can anyone help? The result from the SSMSE query is what I want, yet when I use the very same SELECT statement in my SqlDataSource I don't get any rows returned if the BlogComment count is zero (in the above example I get only the first row). Many thanks for any suggestions!
View 6 Replies
View Related
Jun 8, 2006
I'm working on a procedure that needs to cycle through the records of some raw data and combine the the current record with the datetime field of the prior record. I have been able to write a script to do that with cursors and variables but my problem is it returns each record separately. How do I go about getting the procedure to return all the records as one set of data?
To see what I mean, the following script for the Pubs DB returns each pass through the data as a seperate query. Since I can't do a select *, what approach should I take? If you want the actual script, I would be happy to provide it.
DECLARE authors_cursor CURSOR
FOR SELECT * FROM authors
OPEN authors_cursor
FETCH NEXT FROM authors_cursor
WHILE @@FETCH_STATUS = 0
begin
FETCH NEXT FROM authors_cursor
end
Close Authors_cursor
deallocate authors_cursor
Thanks in advance
Tony Murunion
View 3 Replies
View Related
Dec 2, 2014
I am trying to find a way to lock rows of data used in a SELECT query from being read by another SELECT query.
I could do a "begin tran - select - update - rollback" sequence but was wondering if there is a cleaner way to do this??
Tried UPDLOCK, ROWLOCK, TABLOCK, HOLDLOCK in multiple variations but none seem to block the select.
View 9 Replies
View Related
Jul 27, 2007
I have a stored procedure which simply does a SELECT on a table. This table has as a column a uniqueidentifier, which is not part of the PK. If I execute this procedure with the SQL Server tools, it works fine and returns the expected results. If I execute this SP with Visual Studio, or ASP.NET, no results are returned and the following comes back:Running [dbo].[spServiceDetail_Get] ( @ServiceDetailID = <DEFAULT>, @VictimWitnessID = <DEFAULT>).ServiceDetailID VictimWitnessID --------------- -------------------------------------- No rows affected.(1 row(s) returned)@RETURN_VALUE = 0Finished running [dbo].[spServiceDetail_Get]. Any ideas what is going wrong? This seems to be a common problem for many of our tables with a uniqueidentifier in them, and is specifically with VS 2005 and ASP.NET ObjectDataSources.
View 2 Replies
View Related
Jul 13, 2015
I'm executing Oracle procedure, which has three OUTPUT parameters and returns results in table type variable. i should not use ODBC, MSDAORA providers to call the procedure. So I'm planning to using Oracle OLEDB provider. I'm able to execute the procedure successfully, but when i do check (while dr.Read()) its not returning any records. But I know as per stored procedure results, it should return 66 records.
Dim conn As New OleDbConnection
Dim cmd As New OleDbCommand
Dim dr As OleDbDataReader
Dim QSQL As String
[code]...
View 10 Replies
View Related
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
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
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
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
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
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
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
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
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
Mar 25, 2015
I have four tables: Customer (CustomerId INT, CountyId INT), County (CountyId INT), Search(SearchId INT), and SearchCriteria (SearchCriteriaId INT, SearchId INT, CountyId INT, [others not related to this]).
I want to search Customer based off of the Search record, which could have multiple SearchCriteria records. However, if there aren't any SearchCriteria records with CountyId populated for a given Search, I want it to assume to get all Customer records, regardless of CountyId.
Right now, I'm doing it this way.
DECLARE @SearchId INT = 100
SELECT * FROM Customer WHERE
CountyId IN
(
SELECT CASE WHEN EXISTS(SELECT CountyId FROM SearchCriteria WHERE SearchId = @SearchId)
THEN SearchCriteria.CountyId
[Code] .....
This works; it just seems cludgy. Is there a more elegant way to do this?
View 4 Replies
View Related
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
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
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
