Problem Returning Rows From SPROC

Jul 23, 2005

Heres my problem, the first part selects a row from the database, if
there is no row with the criteria it inserts a row and then returns it,
the problem is the IF statement that inserts the row, never returns the
select after it. if there is a row initially in the database, it
returns the right information, I just can't get it to return the row
after inserting it. Anyone know what the problem could be?

Stored procedure:

ALTER PROCEDURE dbo.CheckCurrentPayPeriod
(@UserID varchar(50))
AS

BEGIN
-- This SP checks to see if the current PayPeriod exists,
-- if not it will create the payperiod for them and return

DECLARE @appStartDate DATETIME
DECLARE @dt DATETIME
DECLARE @rows int
SET @appStartDate = (SELECT PayPeriodStart FROM PayPeriodStart)
SET @dt = GETDATE()

SELECT
UserID
FROM
PayPeriod
WHERE
(PeriodStart <= CONVERT(varchar(10), @dt, 101)) AND (PeriodEnd >=
CONVERT(varchar(10), @dt, 101)) AND (UserID = @UserID)

-- Inserts their new PayPeriod
DECLARE @PayPeriodID int
if (@@ROWCOUNT = 0)
BEGIN
DECLARE @sDate datetime
DECLARE @eDate datetime
SET @sDate = @appStartDate
SET @eDate = DATEADD(day, 13, @sDate)

INSERT INTO
PayPeriod
(UserID, PeriodStart, PeriodEnd)
VALUES
(@UserID, @sDate, @eDate)

/*EXEC @PayPeriodID = InsertPayPeriod @UserID, @sDate, @eDate*/

SET @PayPeriodID = @@IDENTITY

SELECT * FROM PayPeriod WHERE PayPeriodID = @PayPeriodID

RETURN

END
else
RETURN
END

View 1 Replies


ADVERTISEMENT

Returning A String From A Sproc

Sep 8, 2006

I am having problems returning a string from a sproc. What I want to do is to be able to create an XML Doc and return it. I have tried the following:

select
@error = @@error,
@row_count = @@rowcount

set @outgoingXML = '<return_code><error_code>' + Convert(varchar, @error) + '</error_code><row_count>' + Convert(varchar, @row_count) + '</row_count></return_code>'

return @outgoingXML

where @outgoingXML is an output parameter. And I have tried:

set @serilializedReturnCode = '<return_code><error_code>' + Convert(varchar, @error) + '</error_code><row_count>' + Convert(varchar, @row_count) + '</row_count></return_code>'

return @serilializedReturnCode

where @serilializedReturnCode is locally declared as varchar(250)

This is the error I get:

Syntax error converting the varchar value '<return_code><error_code>0</error_code><row_count>0</row_count></return_code>' to a column of data type int.

View 15 Replies View Related

Decrypt Sproc Returning NULL To Non DBO.

Nov 28, 2006

I'm still having issues with this despite my attempts to resolve. I even
have "with exec as dbo" in my sproc, and and "exec as dbo" in my execution,
but still the encrypted data returns nulls when I exec as a user other than
DBO. Below is precisely what I have done. All ideas are welcomed.

TIA, ChrisR


--If there is no master key, create one now

IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD =
'23987hxJKL95QYV4369#ghf0%94467GRdkjuw54ie5y01478d Dkjdahflkujaslekjg5k3fd117
r$$#1946kcj$n44ncjhdlj'
GO

CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Employee Social Security Numbers';
GO

CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO

USE [AdventureWorks];
GO

-- Create a column in which to store the encrypted data
ALTER TABLE HumanResources.Employee
ADD EncryptedNationalIDNumber varbinary(128);
GO

-- Open the symmetric key with which to encrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;

-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
NationalIDNumber);
GO

-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
GO

-- Now list the original ID, the encrypted ID, and the
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.

alter procedure getDecryptedIDNumber
with exec as owner
as
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS "Encrypted ID Number",
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS "Decrypted ID Number"
FROM HumanResources.Employee;
GO

/*works for me, shows the decrypted data*/

exec getDecryptedIDNumber

USE [master]
GO

CREATE LOGIN [test] WITH PASSWORD=N'test',
DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [AdventureWorks]
GO

CREATE USER [test] FOR LOGIN [test]
GO

use [AdventureWorks]
GO

GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
GO

GRANT IMPERSONATE ON USER:: dbo TO test;
GO

/*Now, open up a "file/new/DB Engine Query" and login with the test login*/
exec as user = 'dbo'
exec getDecryptedIDNumber

/*This returns NULL values where it should show the decrypted data*/

View 1 Replies View Related

Sproc Is Returning Output Parameter Inconsistently

Mar 6, 2015

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.

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.

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).

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. I have been unable to replicate this using a test app and our test databases. Have observed selects failing to find even though the data is there, like this before?

View 1 Replies View Related

Returning Rows From A Query

Oct 22, 2001

Greetings!

I need to run a select statement that only returns 50 rows. How do I limit the amount of rows returned? Normally the query will return hundreds of rows but all I need is the first 50 it retrieves. I have looked in the BOL and can only find help with a block cursor not just a query.

Thanks a million

zachary

View 1 Replies View Related

Joins And Returning Rows

Mar 7, 2008

Using MS SQLServer 2000

Is there a way to create a query that will return only 1 row within a join for example:

select a,b,c,d
from tbl1
inner join tbl2 on top 1 tbl2.a = tbl1.a <-- return only the top record here...

I dont have too much of a problem with joins... however I was wondering if there was a mechanism to just specify what would be the top most record from a join clause.

thanks
Kevin

View 1 Replies View Related

Returning The Newest Rows

Jun 30, 2006

I have a query set up that returns the data that I would like, but Iwould only like the latest data for each vehicle number. The query Ihave set up isSELECT TOP 100 PERCENT dbo.vwEvents.EventName,dbo.luSessionAll.SessionName, dbo.luOuting.OutingNumber,dbo.luVehicle.VehicleName, dbo.luOuting.OutingID,dbo.tblOutings.OutingStartTime,dbo.tblSessions.Ses sionDate,dbo.tblSessions.SessionStartTimeFROM dbo.vwSessions INNER JOIN dbo.vwEvents ONdbo.vwSessions.Event = dbo.vwEvents.EventIDINNER JOINdbo.luSessionAll ON dbo.vwEvents.EventID =dbo.luSessionAll.Event INNER JOINdbo.luOuting ON dbo.luSessionAll.SessionID =dbo.luOuting.SessionID INNER JOINdbo.luVehicle ON dbo.luSessionAll.Vehicle =dbo.luVehicle.VehicleID INNER JOINdbo.tblOutings ON dbo.luOuting.OutingID =dbo.tblOutings.OutingID INNER JOINdbo.tblSessions ON dbo.tblOutings.[Session] =dbo.tblSessions.SessionIDGROUP BY dbo.vwEvents.EventName, dbo.luSessionAll.SessionName,dbo.luOuting.OutingNumber, dbo.luVehicle.VehicleName,dbo.luOuting.OutingID, dbo.tblOutings.OutingStartTime,dbo.tblSessions.SessionStartTime, dbo.tblSessions.SessionDateORDER BY dbo.luVehicle.VehicleName, dbo.tblSessions.SessionDate,dbo.tblSessions.SessionStartTime, dbo.tblOutings.OutingStartTimethis returns all the outings. I would like the outing that has, inorder of importance, the latest session date, latest session time andlatest outing start time. Outing start time can sometimes be <<Null>>but the other two always have values. How would I go about doing this?thanks in advance for any help

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

Returning Only The Header Row Instead Of All The Relevant Rows.

Aug 9, 2007

Hi All:
My below sub in application is returning only the Header Row instead of the relevant rows I guess therez problem in my "str" Syntax. However I fail to understand where exactly is it faultering. Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
Try
con.Open()Dim empID As String = txtEmpID.Text
Dim FName As String = txtFName.Text
Dim LName As String = txtLName.TextDim str As String = "Select Emp_ID, Emp_FName,Emp_LName,Emp_Address1,Emp_HNo,Emp_MNo from Emp_Details where Emp_ID='" & empID & " ' Or Emp_LName='" & LName & " ' Or Emp_FName=' " & FName & "'"""Dim da1 As New SqlDataAdapter(str, con)
da1.Fill(ds, "Emp_Details")
dgEmp.DataSource = ds
dgEmp.DataMember = "Emp_Details"
dgEmp.DataBind()
Finally
con.Close()
End Try
End Sub
 
Thanks in Advance for your quick help.
 Regards,
Brandy

View 2 Replies View Related

Returning Amount Of Rows From Sqldatasource

Oct 10, 2007

TotalSelected.Value = SqlDataSource1.SelectCommand = "SELECT COUNT(*) FROM tblNews";
 
the reason i am tring to do this is so if i can find out the amount of rows before sqldatasource selects for details view then i can make the sqldataesource select depends on total minus 5 so e.g. if total 200 then - 5 so i can select bottom 195 so it misses top 5 for details view any1 any ideas?
 Thanks Andy,

View 5 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 The Primary Key Of The Last Rows Updated

Apr 28, 2005

I would like to return the the Primary Key of the row altered from this query - what changes do I need to make?

UPDATE Members SET LastLog = @time WHERE UserName=@user AND Password=@pass;


Thanks in advance,

View 6 Replies View Related

ExecuteNonQuery Not Returning Rows Affected

Mar 13, 2006

I was racking my brains trying to figure out why SomeCommand.ExecuteNonQuery() was not returning any rows...
SQL Server 2005 likes to put the SET NOCOUNT ON statement in every stored procedure you write.  By hiding the count of records touched by your query, you also disable the results to be consumed by your application.
So I don't recommend using this statement for your stored procedures and ASP.NET applications, as this functionality is fairly critical for error trapping.

View 2 Replies View Related

Returning A Subset Of Rows From A Query

Apr 19, 2001

This question has been posted on the site before but I could not find any resolution....I want to return rows 11 - 20 from a query that returns 100 records without using a cursor or temp table.

The closest query I have found is a query that numbers the rows, but I can't seem to use rownumber in a between clause...

Use Pubs
SELECT emp_id, lname, fname, job_id,
(SELECT COUNT(*) FROM employee e2 WHERE e2.emp_id <= e.emp_id AND e2.job_id = 10) AS rownumber
FROM employee e
WHERE job_id = 10
ORDER BY emp_id

Thanks,

Jim

View 1 Replies View Related

Returning Multiple Rows From 2 Tables

Aug 29, 2005

I have two tables and I want to return data from both. Currently my select statement is returning just 1 child record for each parent record and I want to return all child records that match the parent record.

Here's a sample of my tables/data/etc.

t1
------------
speciesid | species
1 | Mammals
2 | Rodents
3 | Reptiles


t2
---------
animalid | animal
3 | Skink
3 | Iguana
3 | Rattlesnake
2 | Meerkat
1 | Hippo
1 | Elk

What I want to do is pull up a list of all the species and under each list all the animals currently listed under that species.

So the result I want should look like:
Mammals (Hippo, Elk)
Reptiles (Skink, Iguana, Rattlesnake)
Rodents (Meerkat)

so currently I have:
SELECT A.animalid, S.speciesid, A.animal, S.species from t2 as A, t1 as S where S.speciesid=A.animalid order by species

this works great, it's just that it only returns one animal instead of all of the animals. Any help would be appreciated.

View 4 Replies View Related

Returning A Set Of Randomly Selected Rows?

Jun 13, 2002

Say you have a table that contains 1000 rows, you create a simple select statement with a where clause that returns 100 or so of those rows. Easy enough.

Now, let's say that you wish to modify that select so that out of the 100 rows that match the where clause criteria, you only wish to return 10 rows randomly (i.e., you could run this query multiple times and get different results). How exactly would you go about doing this, efficiently?

I've thought about creating a stored procedure that will query the initial 100 rows into a temp table with an additional column (to number the rows from 1 to 100). Then setting up a loop (10 iterations) that will then generate a random number from 1 to 100 and select that row number into another temp table. At the end of the loop I'll have my table of randomly selected records. I am sure there is probably a better way to do this...

~Mike

View 2 Replies View Related

Query Returning Duplicate Rows

Oct 6, 2004

I'm trying to write a query that will return rows within a specified range and print to a Crystal Report. When I run the query, it produces 2 row of everything. I would use the SELECT DISTINCT clause, but Crystal Reports will not let me edit the Select statement. But I can edit the FROM, WHERE and ORDER BY clauses. I think the problem is in my INNER JOINS but I'm having a problem figuring it out. Can someone please guide me in the right direction.

SELECT
DrawingVouchers."PlayerID",
DrawingVoucherNumbers."PromoID", DrawingVoucherNumbers."VoucherNumber", DrawingVoucherNumbers."IssueDate", DrawingVoucherNumbers."UserID",
CDS_PLAYER."LastName", CDS_PLAYER."FirstName",
CDS_ACCOUNT."Address1A", CDS_ACCOUNT."City1", CDS_ACCOUNT."State1", CDS_ACCOUNT."Zip1"
FROM
{ oj (("WinOasis"."dbo"."DrawingVouchers" DrawingVouchers INNER JOIN "WinOasis"."dbo"."DrawingVoucherNumbers" DrawingVoucherNumbers ON
DrawingVouchers."PlayerID" = DrawingVoucherNumbers."PlayerID")
INNER JOIN "WinOasis"."dbo"."CDS_PLAYER" CDS_PLAYER ON
DrawingVoucherNumbers."PlayerID" = CDS_PLAYER."Player_ID")
INNER JOIN "WinOasis"."dbo"."CDS_ACCOUNT" CDS_ACCOUNT ON
CDS_PLAYER."Player_ID" = CDS_ACCOUNT."Primary_ID"}
WHERE
DrawingVoucherNumbers."VoucherNumber" >= 37806 AND
DrawingVoucherNumbers."VoucherNumber" <= 37813

View 2 Replies View Related

Left Join Not Returning Rows

Nov 3, 2005

SELECT
[tblSections].[pageTitle],
[tblSections].[sectionURL],
[tblSectionContents].[articleID],
[tblSectionContents].[fileID],
[tblSectionContents].[linkID],
[tblCopy].[copyText],
[tblFiles].[fileName],
[tblFiles].[fileCaption],
[tblGroupings].[grouping],
[tblLinks].[linkURL]
FROM [tblSections]
LEFT JOIN [tblSectionContents] ON [tblSectionContents].[sectionID] = [tblSections].[id]
LEFT JOIN [tblCopy] ON [tblSectionContents].[articleID] = [tblCopy].[id]
LEFT JOIN [tblFiles] ON [tblSectionContents].[fileID] = [tblFiles].[id]
LEFT JOIN [tblGroupings] ON [tblFiles].[groupingID] = [tblGroupings].[id]
LEFT JOIN [tblLinks] ON [tblSectionContents].[linkID] = [tblLinks].[id]
WHERE [tblSections].[id]=2
ORDER BY
[tblSectionContents].[articleID],
[tblSectionContents].[fileID],
[tblSectionContents].[linkID]


If I pass it the ID of a section that has files or copy or [stuff in other tables] attached, then I get a result set that makes sense.

But if I pass it a section ID that doesn't reference any other content tables (ie: the section just has a title and a link URL), I don't get anything back.

Shouldn't it should still get me the fields from the row in tblSections that matches the ID I'm passing it?

How can I make it so that it does?

Thanks :)

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

Count - Returning 0 For Rows With Null

Oct 8, 2014

Is there a way to allow the count () to return a 0 for the rows with a NULL value instead of not returning that row?

View 15 Replies View Related

Multi-table UDF Not Returning All Rows

Sep 7, 2005

I've been tearing my hair out over this UDF. The code works within astored procedure and also run ad-hoc against the database, but does notrun properly within my UDF. We've been using the SP, but I do need aUDF instead now.All users, including branch office, sub-companies and companies and soon up the lines are in the same table. I need a function which returnsa row for each level, eventually getting to the master company all theway at the top, but this UDF acts as though it can't enter the loop andonly inserts the @userID and @branchID rows. I have played with theWHILE condition to no avail.Any ideas on what I am missing?(Running against SQL Server 2000)---------------------------------------------------ALTER FUNCTION udfUplineGetCompany (@userID int)RETURNS @upline table (companyID int, companyname varchar(100), infovarchar(100))ASBEGINDECLARE @branchID intDECLARE @companyID intDECLARE @tempID int--Insert the original user dataINSERT INTO @uplineSELECT tblusersid, companyname, 'userID'FROM tblusersWHERE tblusersid = @useridSELECT @branchID = tblUsers.tblUsersIDFROM tblUsersINNER JOIN tblUsersUsersLnkON tblUsers.tblUsersID = tblUsersUsersLnk.tblUsersID_ParentWHERE tblUsersUsersLnk.tblUsersID_Child = @userid--Up one levelINSERT INTO @uplineSELECT tblusersid, companyname, 'branchID'FROM tblusersWHERE tblusersid = @branchidSET @tempID = @branchIDWHILE @@ROWCOUNT <> 0BEGINSELECT @companyID = tblUsers.tblUsersIDFROM tblUsersINNER JOIN tblUsersUsersLnkON tblUsers.tblUsersID = tblUsersUsersLnk.tblUsersID_ParentWHERE tblUsersUsersLnk.tblUsersID_Child = @tempIDAND tblUsersId <> 6--Insert a row for each level upINSERT INTO @uplineSELECT tblusersid, companyname, 'companyID'FROM tblusersWHERE tblusersid = @companyIDSET @tempID = @companyIDENDRETURNEND

View 2 Replies View Related

LIKE Using Subquery Returning Multiple Rows

Jan 30, 2008



This is probably very elementary to someone more experienced, but I'm having a hard time coming up with a way to do the following:


SELECT * FROM Transactions
WHERE MyField LIKE (SELECT MyValues FROM SearchValues)

But I can't because the subquery will return multiple rows. That's all I'm really trying to do - search all the rows in Transactions.MyField for any of the search values returned from the subquery.

And I can't restructure the SearchValues table to combine them into a single-row comma-delimited field or anything like that.

Any help would be appreciated-

Kenneth

View 11 Replies View Related

Returning Parent / Child Rows

Mar 3, 2008

Hi,
I have the following table


JobID JobType JobName ParentJobID etc...
12345 Disconnection MyJob1
98475 New Pilot MyJob2 12345
85746 Streetlight MyJob3 98475




etc

I need a query that returns all the jobs that relate to a specific job

e.g if I pick 12345 I get the following:

12345 Disconnection MyJob1
98475 New Pilot MyJob2
85746 Streetlight MyJob3

As they are all related records.

Can anyone help

Thanks

View 3 Replies View Related

Returning Rows From A Stored Procedure

Sep 7, 2007

If stored procedure A EXECUTEs stored procedure B, and stored procedure B does a select statement which returns a dataset, how does stored procedure A access that data ?

View 12 Replies View Related

Error Returning Rows Using Enterprise Manager In SQL 7

Jan 15, 2001

Lately I noticed that I cannot return any rows using enterprise manager. The version I am using is MS-SQL 7.0 with SP 1 on NT. This will return an error:-

An unexpected error happened during this operatation.
[Query]-Query Designer encountered a query error.
Unspecified error.

I can return rows using another computer with enterprise manager installed.

Any help appreciated.

Tony

View 2 Replies View Related

SQL 2012 :: Table (with Zero Rows) Not Returning Any Result

Jun 26, 2014

I am trying to run queries on a table (table has zero rows). Inspite of giving 0 rows returned the query keeps on running and I have to cancel it. I tried inserting a dummy row into the table but even the insert operation is taking too long.Every query which I hit on the table just keeps on running without giving any result.

But this is not the case with other tables in the database.They are all running fine giving proper results. But this one table is behaving funny.

View 3 Replies View Related

Returning Multiple Rows From A Stored Procedure

May 22, 2006

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 5 Replies View Related

Sql Question - Returning Multiple Rows As One Record

Jul 20, 2005

Hi,In the process of localizing the 'regions' table, we added three newtables. The localized data will be stored in the TokenKeys andTokenValues tables. It would be easier if we did away with theTokeyKeys/TokenValues tables and just added a localeid in the regionstable, but this is the desired schema by the client. Here's theschema:Table: regionsid nameabbreviation1 United StatesUSTable: localesid locale1 en_US2 fr_CATable: TokenKeysid key1 db.regions.name2 db.regions.abbreviationTable: TokenValuesid keyid valuelocaleid1 1 Etas Unis22 2 EU2The old sql was simply this:select name, abbreviation from regionswhich returns:United States, USBut the new sql needs to link in the localized data from the tokeykeysand tokenvalues tables using the localeid... I’m trying to figure outwhat the sql statement would look like to return this:Etats Unis, EU (This is supposed to be the French version)My confusion is we are trying to return multiple column values fromthe same column (TokenValues.value) and make them act as separatecolumns in the same record, like it was with the original.Thanks

View 1 Replies View Related

Stored Procedure Returning Multiple Rows

Oct 23, 2007

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 3 Replies View Related

Returning Table Rows From Custom Code?

Sep 28, 2007



I wish to call a custom code function from a table control that would return rows of data to be displayed in the table. Is this possible?

Specifically, I'd like to pass a large text string to the function, have the function break the string into smaller strings, and then have the smaller strings displayed in the table. The number of lines returned may vary, depending on the original string passed in.

View 5 Replies View Related

Update The Selected Rows Before Returning Them In One Command.

Mar 14, 2008



Hello,

I have an external process that polls message rows from a table. apon taking them, it also needs to mark them as taken. there is a status column in the table. marking messages as taken will change the value of status.

How can i perform both these operations in one command? Select the top x rows where their status is equal 1, then update the status of those same rows to a value of 5 for example.

I could iterate through the result of hte intial select and change the status 1 by 1 using a cursor, but this seems like a slow option.

Any other ideas?

Thank you.

View 4 Replies View Related

Stored Procedure Not Returning Rows In Web Application

Nov 11, 2006

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 7 Replies View Related

DFT OLEDB Source Returning Empty Rows

Nov 19, 2007

I have had at least 2 occurences of a DFT OLEDB source returning the correct number of rows but all rows are empty (they contain zeros or ''). This has happened in two different places in different SSIS packages within an ETL task. In one case the source was on a different server running SQLServer and in the other it was a different database on the same server as the SSIS package. This occured running on different servers, one with SQL 2005 SP2 and the other without. Both are 64 bit AMD systems running 64 bit SQL 2005. As there is a derived column transformation that has performed the derivation on the blank columns I assume the problem is with the OLEDB source but it could be with the data stream from the OLEDB source to to whatever follows. Has anyone else had this problem and does anyone know of a fix?

View 7 Replies View Related







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