Function Issue - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Function Issue

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-02-22 : 16:09:32

Hi All
I have a function which is supposed to pick records associated with a request in a table and then insert into a single record.I was assuming that the function is working fine until today when a user has shown me a problem that iam unable to fix

function

CREATE Function [dbo].[Stoploss_Maintenace_DISTRIBUTION] ( @Requestid INT)
RETURNS @STOPLOSS_RECEPIENTS TABLE
(
Requestid numeric(9)  NULL,
rptdis1 char(1) NULL,
recipnm1  varchar(100) NULL,
emailadr1 varchar(100) NULL,
rptdis2 char(1) NULL,
recipnm2  varchar(100) NULL,
emailadr2 varchar(100) NULL,
rptdis3 char(1) NULL,
recipnm3  varchar(100) NULL,
emailadr3 varchar(100) NULL,
rptdis4 char(1) NULL,
recipnm4  varchar(100) NULL,
emailadr4 varchar(100) NULL,
rptdis5 char(1) NULL,
recipnm5  varchar(100) NULL,
emailadr5 varchar(100) NULL,
rptdis6 char(1) NULL,
recipnm6  varchar(100) NULL,
emailadr6 varchar(100) NULL,
rptdis7 char(1) NULL,
recipnm7  varchar(100) NULL,
emailadr7 varchar(100) NULL,
rptdis8 char(1) NULL,
recipnm8  varchar(100) NULL,
emailadr8 varchar(100) NULL
)
AS 
BEGIN


-- The temp should be like this--
DECLARE @tmp TABLE
(
ReqNo INT identity(1,1),
RequestID numeric(9) NULL,
rptdis char(2) NULL,
recipnm  varchar(100) NULL,
emailadr varchar(100) NULL
)
---------------------------------

INSERT INTO @tmp (RequestID, RptDis, recipnm, EmailAdr)
SELECT RequestID, RptDis, recipnm, EmailAdr
FROM Report_Distribution
WHERE RequestID = @RequestID

INSERT INTO @STOPLOSS_RECEPIENTS
(Requestid
,rptdis1
,recipnm1
,emailadr1
,rptdis2
,recipnm2
,emailadr2
,rptdis3
,recipnm3
,emailadr3
,rptdis4
,recipnm4
,emailadr4
,rptdis5
,recipnm5
,emailadr5
,rptdis6
,recipnm6
,emailadr6
,rptdis7
,recipnm7
,emailadr7
,rptdis8
,recipnm8
,emailadr8)
SELECT
 RequestID = (SELECT ISNULL(RequestID,NULL) FROM @tmp WHERE ReqNo = 1)
 ,RptDis1 = (SELEcT ISNULL(RptDis,'') FROM @tmp WHERE ReqNo = 1)
 ,RecipientName1 = (SELEcT ISNULL(recipnm,'') FROM @tmp WHERE ReqNo = 1)
 ,EmailAdr1 = (SELEcT ISNULL(EmailAdr,'') FROM @tmp WHERE ReqNo = 2)
 ,RptDis2 = (SELEcT ISNULL(RptDis,'') FROM @tmp WHERE ReqNo = 2)
 ,RecipientName2 = (SELEcT ISNULL(recipnm,'') FROM @tmp WHERE ReqNo = 2)
 ,EmailAdr2 = (SELEcT ISNULL(EmailAdr,'') FROM @tmp WHERE ReqNo = 2)
 ,RptDis3 = (SELEcT ISNULL(RptDis,'') FROM @tmp WHERE ReqNo = 3)
 ,RecipientName3 = (SELEcT ISNULL(recipnm,'') FROM @tmp WHERE ReqNo = 3)
 ,EmailAdr3 = (SELEcT ISNULL(EmailAdr,'') FROM @tmp WHERE ReqNo = 3)
 ,RptDis4 = (SELEcT ISNULL(RptDis,'') FROM @tmp WHERE ReqNo = 4)
 ,RecipientName4 = (SELEcT ISNULL(recipnm,'') FROM @tmp WHERE ReqNo = 4)
 ,EmailAdr4 = (SELEcT ISNULL(EmailAdr,'') FROM @tmp WHERE ReqNo = 4)
 ,RptDis5 = (SELEcT ISNULL(RptDis,'') FROM @tmp WHERE ReqNo = 5)
 ,RecipientName5 = (SELEcT ISNULL(recipnm,'') FROM @tmp WHERE ReqNo = 5)
 ,EmailAdr5 = (SELEcT ISNULL(EmailAdr,'') FROM @tmp WHERE ReqNo = 5)
 ,RptDis6 = (SELEcT ISNULL(RptDis,'') FROM @tmp WHERE ReqNo = 6)
 ,RecipientName6 = (SELEcT ISNULL(recipnm,'') FROM @tmp WHERE ReqNo = 6)
 ,EmailAdr6 = (SELEcT ISNULL(EmailAdr,'') FROM @tmp WHERE ReqNo = 6)
 ,RptDis7 = (SELEcT ISNULL(RptDis,'') FROM @tmp WHERE ReqNo = 7)
 ,RecipientName7 = (SELEcT ISNULL(recipnm,'') FROM @tmp WHERE ReqNo = 7)
 ,EmailAdr7 = (SELEcT ISNULL(EmailAdr,'') FROM @tmp WHERE ReqNo = 7)
 ,RptDis8 = (SELEcT ISNULL(RptDis,'') FROM @tmp WHERE ReqNo = 8)
 ,RecipientName8 = (SELEcT ISNULL(recipnm,'') FROM @tmp WHERE ReqNo = 8)
 ,EmailAdr8 = (SELEcT ISNULL(EmailAdr,'') FROM @tmp WHERE ReqNo = 8)

RETURN

END

Table

CREATE TABLE [dbo].[DISTRIBUTION](
	[requestid] [numeric](18, 0) NOT NULL,
	[rptdis] [char](1) NOT NULL,
	[recipnm] [varchar](50) NULL,
	[emailadr] [varchar](75) NULL

when i execute the function the data comes like

Data
147853 B test1 test2@abc.com C Test2 test2@abc.com N test3 test3@abc.com R Ram,kolla Ram_kolla@abc.com S test4 test4@abc.com T test6 test6@abc.com U Ram,Kolla Ram_kolla@abc.com V test5 test5@abc.com

The problem is that test 1 somehow the email that comes is of test2
Iam not sure what is incorrect in my function that is ignoring test1 email and inserting test2email

Please need help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-22 : 17:30:07

you've same expression for both
it should be this instead


..
EmailAdr1 = (SELEcT ISNULL(EmailAdr,'') FROM @tmp WHERE ReqNo = 21)
 ,RptDis2 = (SELEcT ISNULL(RptDis,'') FROM @tmp WHERE ReqNo = 2)
 ,RecipientName2 = (SELEcT ISNULL(recipnm,'') FROM @tmp WHERE ReqNo = 2)
 ,EmailAdr2 = (SELEcT ISNULL(EmailAdr,'') FROM @tmp WHERE ReqNo = 2
..

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-02-23 : 08:08:41

That was easy.Dont understand how i missed this

Thanks

quote:
Originally posted by visakh16

you've same expression for both
it should be this instead

..
EmailAdr1 = (SELEcT ISNULL(EmailAdr,'') FROM @tmp WHERE ReqNo = 21)
,RptDis2 = (SELEcT ISNULL(RptDis,'') FROM @tmp WHERE ReqNo = 2)
,RecipientName2 = (SELEcT ISNULL(recipnm,'') FROM @tmp WHERE ReqNo = 2)
,EmailAdr2 = (SELEcT ISNULL(EmailAdr,'') FROM @tmp WHERE ReqNo = 2
..

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-23 : 08:49:50

no problem

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-23 : 09:10:55

Also, if you want PERFORMANCE and SPEED of your function, rewrite it as an inline TVF.

CREATE FUNCTION dbo.Stoploss_Maintenace_DISTRIBUTION
(
	@RequestID INT
)
RETURNS TABLE
AS
RETURN	(
		SELECT	@RequestID AS RequestID,
			MAX(CASE WHEN SeqNo = 1 THEN RptDis ELSE NULL END) AS RptDis1,
			MAX(CASE WHEN SeqNo = 1 THEN RecipNm ELSE NULL END) AS RecipientName1,
			MAX(CASE WHEN SeqNo = 1 THEN EmailAdr ELSE NULL END) AS EmailAdr1,
			MAX(CASE WHEN SeqNo = 2 THEN RptDis ELSE NULL END) AS RptDis2,
			MAX(CASE WHEN SeqNo = 2 THEN RecipNm ELSE NULL END) AS RecipientName2,
			MAX(CASE WHEN SeqNo = 2 THEN EmailAdr ELSE NULL END) AS EmailAdr2,
			MAX(CASE WHEN SeqNo = 3 THEN RptDis ELSE NULL END) AS RptDis3,
			MAX(CASE WHEN SeqNo = 3 THEN RecipNm ELSE NULL END) AS RecipientName3,
			MAX(CASE WHEN SeqNo = 3 THEN EmailAdr ELSE NULL END) AS EmailAdr3,
			MAX(CASE WHEN SeqNo = 4 THEN RptDis ELSE NULL END) AS RptDis4,
			MAX(CASE WHEN SeqNo = 4 THEN RecipNm ELSE NULL END) AS RecipientName4,
			MAX(CASE WHEN SeqNo = 4 THEN EmailAdr ELSE NULL END) AS EmailAdr4,
			MAX(CASE WHEN SeqNo = 5 THEN RptDis ELSE NULL END) AS RptDis5,
			MAX(CASE WHEN SeqNo = 5 THEN RecipNm ELSE NULL END) AS RecipientName5,
			MAX(CASE WHEN SeqNo = 5 THEN EmailAdr ELSE NULL END) AS EmailAdr5,
			MAX(CASE WHEN SeqNo = 6 THEN RptDis ELSE NULL END) AS RptDis6,
			MAX(CASE WHEN SeqNo = 6 THEN RecipNm ELSE NULL END) AS RecipientName6,
			MAX(CASE WHEN SeqNo = 6 THEN EmailAdr ELSE NULL END) AS EmailAdr6,
			MAX(CASE WHEN SeqNo = 7 THEN RptDis ELSE NULL END) AS RptDis7,
			MAX(CASE WHEN SeqNo = 7 THEN RecipNm ELSE NULL END) AS RecipientName7,
			MAX(CASE WHEN SeqNo = 7 THEN EmailAdr ELSE NULL END) AS EmailAdr7,
			MAX(CASE WHEN SeqNo = 8 THEN RptDis ELSE NULL END) AS RptDis8,
			MAX(CASE WHEN SeqNo = 8 THEN RecipNm ELSE NULL END) AS RecipientName8,
			MAX(CASE WHEN SeqNo = 8 THEN EmailAdr ELSE NULL END) AS EmailAdr8
		FROM	(
				SELECT	RptDis,
					RecipNm,
					EmailAdr,
					ROW_NUMBER() OVER (ORDER BY RptDis) AS SeqNo
				FROM	dbo.Report_Distribution
				WHERE	RequestID = @RequestID
			) AS d
		WHERE	SeqNo BETWEEN 1 AND 8
	)

If you want empty space to be returned instead of NULL, just replace all NULL with '' (two single quotes).

N 56°04'39.26"
E 12°55'05.63"

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-02-23 : 09:38:15

Thank You.it worked.

quote:

Originally posted by SwePeso

Also, if you want PERFORMANCE and SPEED of your function, rewrite it as an inline TVF.

CREATE FUNCTION dbo.Stoploss_Maintenace_DISTRIBUTION
(
	@RequestID INT
)
RETURNS TABLE
AS
RETURN	(
		SELECT	@RequestID AS RequestID,
			MAX(CASE WHEN SeqNo = 1 THEN RptDis ELSE NULL END) AS RptDis1,
			MAX(CASE WHEN SeqNo = 1 THEN RecipNm ELSE NULL END) AS RecipientName1,
			MAX(CASE WHEN SeqNo = 1 THEN EmailAdr ELSE NULL END) AS EmailAdr1,
			MAX(CASE WHEN SeqNo = 2 THEN RptDis ELSE NULL END) AS RptDis2,
			MAX(CASE WHEN SeqNo = 2 THEN RecipNm ELSE NULL END) AS RecipientName2,
			MAX(CASE WHEN SeqNo = 2 THEN EmailAdr ELSE NULL END) AS EmailAdr2,
			MAX(CASE WHEN SeqNo = 3 THEN RptDis ELSE NULL END) AS RptDis3,
			MAX(CASE WHEN SeqNo = 3 THEN RecipNm ELSE NULL END) AS RecipientName3,
			MAX(CASE WHEN SeqNo = 3 THEN EmailAdr ELSE NULL END) AS EmailAdr3,
			MAX(CASE WHEN SeqNo = 4 THEN RptDis ELSE NULL END) AS RptDis4,
			MAX(CASE WHEN SeqNo = 4 THEN RecipNm ELSE NULL END) AS RecipientName4,
			MAX(CASE WHEN SeqNo = 4 THEN EmailAdr ELSE NULL END) AS EmailAdr4,
			MAX(CASE WHEN SeqNo = 5 THEN RptDis ELSE NULL END) AS RptDis5,
			MAX(CASE WHEN SeqNo = 5 THEN RecipNm ELSE NULL END) AS RecipientName5,
			MAX(CASE WHEN SeqNo = 5 THEN EmailAdr ELSE NULL END) AS EmailAdr5,
			MAX(CASE WHEN SeqNo = 6 THEN RptDis ELSE NULL END) AS RptDis6,
			MAX(CASE WHEN SeqNo = 6 THEN RecipNm ELSE NULL END) AS RecipientName6,
			MAX(CASE WHEN SeqNo = 6 THEN EmailAdr ELSE NULL END) AS EmailAdr6,
			MAX(CASE WHEN SeqNo = 7 THEN RptDis ELSE NULL END) AS RptDis7,
			MAX(CASE WHEN SeqNo = 7 THEN RecipNm ELSE NULL END) AS RecipientName7,
			MAX(CASE WHEN SeqNo = 7 THEN EmailAdr ELSE NULL END) AS EmailAdr7,
			MAX(CASE WHEN SeqNo = 8 THEN RptDis ELSE NULL END) AS RptDis8,
			MAX(CASE WHEN SeqNo = 8 THEN RecipNm ELSE NULL END) AS RecipientName8,
			MAX(CASE WHEN SeqNo = 8 THEN EmailAdr ELSE NULL END) AS EmailAdr8
		FROM	(
				SELECT	RptDis,
					RecipNm,
					EmailAdr,
					ROW_NUMBER() OVER (ORDER BY RptDis) AS SeqNo
				FROM	dbo.Report_Distribution
				WHERE	RequestID = @RequestID
			) AS d
		WHERE	SeqNo BETWEEN 1 AND 8
	)

If you want empty space to be returned instead of NULL, just replace all NULL with '' (two single quotes).

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources