Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Stored procedure returning records - some do,some

Author  Topic 

ipisors
Starting Member

39 Posts

Posted - 2015-05-05 : 18:23:25
I've learned from reading that for a stored procedure to return records, I should use a RETURN codes or OUTPUT parameters.

My question here isn't to ask how to do that or even to debate whether this is a good practice, but just....I have a stored procedure that ends with a select statement, and then one tiny insert into a log table. That's the end of it, just as described. And when executed (with passed-in parameters) either in management studio OR used as a source for an SSRS report, IT RETURNS RECORDS, AND IT HAS NO RETURN STATEMENT NOR OUTPUT PARAMETER. Just input parameters and it ends with a select statement.

However, someone else who I work with wrote one and his behaves differently. Although it ends exactly like mine (with a select statement and then after that, a tiny insert into a log table), it also has input parameters and NOT return statement nor output parameters....it does not return any records. I've troubleshot the SQL and I'm positive it returns 17 records (when run as regular query using hardocded where clause values), not executing the stored procedure).

Any ideas as to the difference ???

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-05-05 : 18:26:10
quote:

I've learned from reading that for a stored procedure to return records, I should use a RETURN codes or OUTPUT parameters.


Where did you read that? You don't need to use either to return rows. RETURN is for error codes, OUTPUT is to return scalar variables and the likes. If you want to return a result set, then you use SELECT.

We'll need to see the code in order to help you answer the questions.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

ipisors
Starting Member

39 Posts

Posted - 2015-05-05 : 18:49:06
Hmm, ok guess I was confused then (and am still confused).

Here is the article that says that: https://msdn.microsoft.com/en-us/library/ms188655.aspx

In my current situation, the stored procedures do, in fact, have a Select statement in them (and after that they do nothing prior to End except for inserting data in a report run log table).

The methods we're using to pass data parameters, then convert them to YYYYMMDD ints (which is what our particular table requires), are exactly identical. And I've run the sql with the same parameters we're using...17 records when the sql runs, zero records with SP executes. (on his proc, the one that i'm wondering why returns no records).

Here is the code that returns 17 records:

USE databasename
GO
select distinct
m.medicaidid
, m.firstname
, m.lastname
, m.dateOfbirth
,database.dbo.fnAgeOnExactDate(database.dbo.FnConvDateNull(m.DateOfBirth), getdate()) as 'Age'
, max(c.admitdate)as 'last visit'
,originalbillingname

from claims c
join claimsdetail cd on c.claimsseqnbr = cd.claimsseqnbr join members m on c.MemberNbr = m.MemberNbr join providers p on m.currassignedprovider1 = p.providerNbr where cd.providernumberhcfa = 'value'
and c.admitdate between 20150101 and 20150430
and c.claimtype ='E'
and m.medicaidid <>'999999999'
group by
m.medicaidid
, m.firstname
, m.lastname
, m.dateOfbirth
,originalbillingname


Here is the stored procedure that returns zero records, even when I execute it with date '2015-01-01' through '2015-04-30' and varchar parameter value 'value'
I'm pretty confident in my parameter (dates converted to ints and all that) because I use the exact method in a number of other procedures.


USE [dbname]
GO
/****** Object: StoredProcedure [dbo].[spname] Script Date: 5/5/2015 3:46:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[PU_REPORTING_LOB-PHP_PRVDRUTILIZATION](@ClaimsFromDate date, @ClaimsToDate date, @ProviderNumber Varchar)

AS
BEGIN

-- Insert statements for procedure here
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--(the data in our table for dates is YYYYMMDD as int so we always have to get to the point of using an INT like that)

declare @claimsFromDateINT int
declare @claimsToDateINT int
set @claimsFromDateINT = cast(cast(year(@ClaimsFromDate) as varchar) + right('0' + cast(month(@ClaimsFromDate) as varchar),2)
+ right('0' + cast(day(@ClaimsFromDate) as varchar),2) as int)
set @claimsToDateINT = cast(cast(year(@ClaimsToDate) as varchar) + right('0' + cast(month(@ClaimsToDate) as varchar),2)
+ right('0' + cast(day(@ClaimsToDate) as varchar),2) as int)

select distinct
m.medicaidid
, m.firstname
, m.lastname
, m.dateOfbirth
, c.Memberage
, max(c.admitdate)as 'last visit'
,originalbillingname

from [dbname].dbo.claims c
join [dbname].dbo.claimsdetail cd on c.claimsseqnbr = cd.claimsseqnbr
join [dbname].dbo.members m on c.MemberNbr = m.MemberNbr
join [dbname].dbo.providers p on m.currassignedprovider1 = p.providerNbr
where cd.providernumberhcfa = @ProviderNumber
and c.admitdate between @claimsFromDateINT and @claimsToDateINT
and c.claimtype ='E'
and m.medicaidid <>'999999999'
group by
m.medicaidid
, m.firstname
, m.lastname
, m.dateOfbirth
, c.Memberage
,originalbillingname

order by 5

--finally, log report run for troubleshooting and many other useful purposes
insert [dbo].[tablename] (dateran,userran,reportname)
values (getdate(),SUSER_NAME(),'reportname')

END
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-05-05 : 18:52:31
What is the data type of admitdate? It appears to be an integer according to your first query.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-05-05 : 18:53:53
And I would ignore the first sentence in that article. lol

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

ipisors
Starting Member

39 Posts

Posted - 2015-05-05 : 19:07:26
Yes, the data type of admitdate is INT and they are all populated in the table like YYYYMMDD. So we typically query them as integers. It's pretty ridiculous but....

So in many other procedures, I've used this same method:

1) use a real date as input parameter (for the benefit of calling programs, users, etc)

2) inside the proc, declare an INT variable and assign its value by doing some converting/casting on the date (I know my long cast(cast()) statement can be simplified, but aside from that for now), so that the WHERE clause can then refer to the INT variables.

It works perfectly on a number of other procedures, and I troubleshoot it the same way....by taking the inner SQL of the procedure, putting it in a new query window, and changing the variables to actual INTS (like in the sql from my last post), and voila...same results either way.

but this scenario is acting differently. i cannot for the life of me tell why.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-05-05 : 19:15:44
If the data type is int, you can't then use datetime or date data type as the storage of those data types is completely different. 20140505 is not stored as 20140505 when the data type is datetime. Use the correct data type for your variables. But I would recommend "fixing" the column's data type as it's just plain silly to use integer data type for date/time data.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

ipisors
Starting Member

39 Posts

Posted - 2015-05-05 : 19:19:30
I'm not sure if you saw my code or my last post...But again, this is what I am doing:

1. using date parameters
2. converting those data parameters to INTs, in format, YYYYMMDD
3. then using variables assigned with the value from the conversion in the Where clause.

In other words, a real data gets passed in. (say, 1/1/2015). Then it gets converted to INT 20150101. Then the variable which is populated with the INT 20150101 is what is used in the where clause.

I am actually doing the proper conversion I believe, can you double check that you've read over my code?

I couldn't agree with you more about the table column data type, if that aspect of it was under my control (or any of our control), we certainly would change it but we cannot. We have had to work with dates in this particular table formatted as INT for quite a while now.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-05-05 : 19:30:26
I missed the conversions in your code, so yes I didn't read the whole thing.

Show us the EXEC for the stored procedure. And are you executing the stored procedure in SSMS or the app?

What size is providernumberhcfa? Is it over 30? Are you testing with 'value' or something bigger than 30? I wasn't sure if 'value' was used on SQLTeam to protect an account number.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-05-06 : 04:26:04
quote:
Originally posted by ipisors

even when I execute it with date '2015-01-01' through '2015-04-30'


The only unambiguous format for string dates is 'yyyymmdd' (NO punctuation). Parsing of any other date format relies on the LOCALE setting for your server, the LANGUAGE setting for the currently logged on user etc. etc. etc. All of which may change in the future ... It MIGHT be that your configuration is not parsing '2015-01-01' correctly (although it is very unlikely ... for that particular format). Worth double checking though.

quote:
set @claimsFromDateINT = cast(cast(year(@ClaimsFromDate) as varchar) + right('0' + cast(month(@ClaimsFromDate) as varchar),2)
+ right('0' + cast(day(@ClaimsFromDate) as varchar),2) as int)



looks jolly hard work! I reckon you could do this instead:

CONVERT(INT, convert(VARCHAR(8), @ClaimsFromDate, 112))


quote:
@ProviderNumber Varchar)
cast(year(@ClaimsFromDate) as varchar)


ALWAYS specify a size with VARCHAR declaration. Otherwise you will get the default,which may be too small and your data will be (silently!) truncated.

quote:
I couldn't agree with you more about the table column data type

... looks like the vendor has "magic values" too, which are equally bad. e.g.

and m.medicaidid <>'999999999'

I obviously don't know the data / APP at all, but "unknown" should be NULL not some arbitrary value - look at the damage that using 999999 for "Don't care dates" did as the Year 2000 approached ...

Add this to your colleague's SProc so you can check that the conversions inside the SProc are correct etc.


PRINT '@ProviderNumber='+COALESCE(@ProviderNumber+'[', '[NULL]')
+ ', @claimsFromDateINT='+COALESCE(CONVERT(varchar(20), @claimsFromDateINT)+'[', '[NULL]')
+ ', @claimsToDateINT='+COALESCE(CONVERT(varchar(20), @claimsToDateINT)+'[', '[NULL]')
Go to Top of Page

ipisors
Starting Member

39 Posts

Posted - 2015-05-06 : 15:13:38
Thank you both.

- 'value' was just dummy for the purpose of this post, yes we are testing it with a 6-digit text string.

- EXEC for SP: exec [dbo].[PU_REPORTING_LOB-PHP_PRVDRUTILIZATION] '2015-01-01','2015-04-30','value'
(value is really a 6 digit text string)

- Kristen, interesting comment, I spent SO much time reading a portion of the somewhat overwhelming enormity of debates out there about what type of literal string to pass as a date under what conditions, locale, session-level settings, database-level settings, etc. etc. etc....And I finally had read a comment from some very authoritative-seeming person on stackoverflow who had published books (so their advice HAS to be good, right? <jk grin>), who told me exactly that the only unambiguous string value for date was 'YYYY-MM-DD'. But anyway thank you, I will do some more research follow up on this. But as I mentioned in earlier posts in this thread, I'm testing my procedure and his procedure basically the EXACT same way, this is why I created this thread..I can't figure out why one returns records and one doesn't, even though I'm using the same technique for all potentially doubtful elements, or so I thought: the casting of date to int, the exec, etc.

- as for the conversion of date to int, as I mentioned in OP, I have noticed this easier way and I am planning to validate/study it and probably switch...so yes, I can see my original method gives a chuckle (and well warranted I'm sure). Thanks for the reminder that I need to change simplify this.

- thanks for the size with varchar suggestion. I will implement this immediately.

As with so many SQL issues, I just have to go back to...I have zero control over this table, so while the conversation about nulls vs. dummy values is profitable (and I realize this myself), I can't do anything about it - just solve to the existing structure.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-05-07 : 04:56:31
quote:
Originally posted by ipisors

I finally had read a comment from some very authoritative-seeming person on stackoverflow who had published books (so their advice HAS to be good, right? <jk grin>), who told me exactly that the only unambiguous string value for date was 'YYYY-MM-DD'.


Well ... there is a difference between converting to DATE and converting to DATETIME. DATE accepts 'yyyy-mm-dd',
unambiguously, in some (maybe all??) language settings where DATETIME does not.
I don't know if that is true for all Locales / Languages though, MS seem to use 'yyyy-mm-dd' in all the DATE examples
which suggests that it is unambiguously converted - but at the very least it is inconsistent (between DATE and DATETIME),
and if you were to do a conversion to DATETIME, instead of DATE, one day then you would get different results
which is why I recommend using the "yyymmdd" format as it is guaranteed unambiguous or both DATE and DATETIME.
The situation is basically A Crock ... I have no idea why SQL allows all these different date formats,
which are so dependent on transient session settings, its nuts. But there we are ...

SET LANGUAGE ENGLISH
GO
SELECT CONVERT(date, '20150102'), CONVERT(datetime, '20150102'), CONVERT(date, '2015-01-02'), CONVERT(datetime, '2015-01-02')
-- 2015-01-02 2015-01-02 00:00:00.000 2015-01-02 2015-01-02 00:00:00.000
GO
SET LANGUAGE FRENCH
GO
SELECT CONVERT(date, '20150102'), CONVERT(datetime, '20150102'), CONVERT(date, '2015-01-02'), CONVERT(datetime, '2015-01-02') -- This will be February!! ...
-- 2015-01-02 2015-01-02 00:00:00.000 2015-01-02 2015-02-01 00:00:00.000
SET LANGUAGE ENGLISH
GO
SET DATEFORMAT YDM
GO
-- DATETIME 'yyyy-dd-mm' successfully converted using DATEFORMAT, conversion to DATE did NOT use DATEFORMAT , 'yyyymmdd' ignores DATEFORMAT as expected
SELECT CONVERT(date, '20150102'), CONVERT(datetime, '20150102'), CONVERT(date, '2015-01-02'), CONVERT(datetime, '2015-01-02')
-- 2015-01-02 2015-01-02 00:00:00.000 2015-01-02 2015-02-01 00:00:00.000
GO


There is another unambiguous format, which includes time, the ISO8601 format which is 'YYYY-MM-DDThh:mm:ss[.mmm]'
In this format the hyphen punctuation IS required <sigh!>
There are also unambiguous formats for ODBC/ADO etc. which look like {ts'yyyy-mm-ddhh:mm:ss[.fff]'}, {d'yyyy-mm-dd'}, {t'hh:mm:ss'}

For anything else the MS SQL Docs says:

"Applications that use other APIs, or Transact-SQL scripts, stored procedures, and triggers,
should use the unseparated numeric strings. For example, yyyymmdd as 19980924.
"
(or explicitly CONVERT with appropriate style parameters)
https://msdn.microsoft.com/en-US/library/ms191307.aspx

quote:
I'm testing my procedure and his procedure basically the EXACT same way


Not exactly the same: in one you have Fixed Strings in the other you have Parameters.
It may well be that the default size of the varchar parameter is too short.
Another annoyance of mine that there is no "Verbose warning" setting in SQL that would alert to these type of things.

quote:
I have zero control over this table, so while the conversation about nulls vs. dummy values is profitable (and I realize this myself), I can't do anything about it - just solve to the existing structure.



Yup, understood and sympathise . Was only pointing out the "magic value" thing as an additional example that the Vendor's code has weaknesses.
I'm sure mine does too! its a bit like when you move house and have to then sort through everything in the attic/loft - lots of completely inappropriate/legacy junk accumulated over the years ...
posting examples Vendor code on this forum ahas a similar effect, at least with pedantic people like me
Go to Top of Page
   

- Advertisement -