using parameter to retrieve dataset in sp

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 2005 Forums

Transact-SQL (2005)

using parameter to retrieve dataset in sp

Author

Topic

sharona
Yak Posting Veteran

75 Posts

Posted - 2010-04-22 : 11:27:47

I am missing something here. I want to use a parameter to retrieve current data if n is entered and history when y is entered. this is what i have so far,

@Start_Date datetime,
@End_date datetime,
@History varchar(1))
AS
SELECT
custtype = CASE WHEN @history = 'y' then
dbo.AlertHist.AlertNo AS AH_ALERTNO,
dbo.AlertHist.Account AS AH_ACCT,
dbo.AlertHist.Cust AS AH_CUST_ID,
dbo.Customer.Id AS AH_CUSTID,
dbo.Customer.Name AS AH_CUSTNAME,
dbo.AlertHist.WLCode AS AH_WLCODE,
dbo.AlertHist.[Desc] AS AH_DESC,
dbo.AlertHist.Status AS AH_STATUS,
dbo.AlertHist.CreateDate AS AH_CREATE_DATE,
dbo.AlertHist.LastOper AS AH_LASTOPER,
dbo.AlertHist.LastModify AS AH_LASTMODIFY,
dbo.AlertHist.ReviewState AS AH_REVIEW_STATE,
dbo.AlertHist.ReviewOper AS AH_REVIEW_OPER,
dbo.AlertHist.ReviewTime AS AH_REVIEW_TIME,
dbo.AlertHist.App AS AH_APPROVE,
dbo.AlertHist.AppTime AS AH_APPROVE_TIME,
dbo.AlertHist.AppOper AS AH_APPOPER,
dbo.AlertHist.Annotation AS AH_ANNOTATION,
end,
case when @history ='N' then */
dbo.Alert.AlertNo AS A_ALERTNO,
dbo.Alert.Account AS A_ACCT,
dbo.Alert.Cust AS A_CUST,
dbo.Alert.WLCode AS A_WLCODE,
dbo.Watchlist.Title,
dbo.Alert.[Desc] AS A_DESC,
dbo.Alert.Status AS A_STATUS,
dbo.Alert.CreateDate AS A_CREATE_DATE,
dbo.Alert.LastOper AS A_LASTOPER,
dbo.Alert.LastModify AS A_LAST_MODIFIED,
dbo.Alert.ReviewState AS A_REVIEW_STATE,
dbo.Alert.ReviewOper AS A_REVIEW_OPER,
dbo.Alert.ReviewTime AS A_REVIEW_TIME,
dbo.Alert.App AS A_APPROVE,
dbo.Alert.AppOper AS A_APP_OPER,
dbo.Alert.AppTime AS A_APP_TIME,
dbo.Alert.Annotation AS A_ANNOTATION,
Customer.Id AS A_CUST,
Customer.Name AS A_CUST_NAME
--end,
FROM
dbo.Alert INNER JOIN
dbo.Watchlist ON dbo.Alert.WLCode = dbo.Watchlist.WLCode left outer JOIN
dbo.Customer ON dbo.Alert.Cust = dbo.Customer.Id

/*RIGHT OUTER JOIN
dbo.AlertHist LEFT OUTER JOIN
dbo.Customer ON dbo.AlertHist.Cust = dbo.Customer.Id ON dbo.Alert.AlertNo = dbo.AlertHist.AlertNo
*/
where Alert.CreateDate between @Start_Date and @End_date

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-22 : 12:49:57

Better make two select statements and execute the needed one using IF statement.

No, you're never too old to Yak'n'Roll if you're too young to die.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-04-22 : 12:50:35

try something like:

@Start_Date datetime,
@End_date datetime,
@History varchar(1))
AS

IF  @history = 'y' 
BEGIN
	SELECT 
		dbo.AlertHist.AlertNo AS AH_ALERTNO, 
		dbo.AlertHist.Account AS AH_ACCT, 
		dbo.AlertHist.Cust AS AH_CUST_ID, 
		dbo.Customer.Id AS AH_CUSTID, 
		dbo.Customer.Name AS AH_CUSTNAME, 
		dbo.AlertHist.WLCode AS AH_WLCODE, 
		dbo.AlertHist.[Desc] AS AH_DESC,
		dbo.AlertHist.Status AS AH_STATUS, 
		dbo.AlertHist.CreateDate AS AH_CREATE_DATE, 
		dbo.AlertHist.LastOper AS AH_LASTOPER, 
		dbo.AlertHist.LastModify AS AH_LASTMODIFY, 
		dbo.AlertHist.ReviewState AS AH_REVIEW_STATE, 
		dbo.AlertHist.ReviewOper AS AH_REVIEW_OPER, 
		dbo.AlertHist.ReviewTime AS AH_REVIEW_TIME, 
		dbo.AlertHist.App AS AH_APPROVE, 
		dbo.AlertHist.AppTime AS AH_APPROVE_TIME, 
		dbo.AlertHist.AppOper AS AH_APPOPER, 
		dbo.AlertHist.Annotation AS AH_ANNOTATION
	FROM
		-- Insert Table(s) Here
END
ELSE
BEGIN
	SELECT
		dbo.Alert.AlertNo AS A_ALERTNO, 
		dbo.Alert.Account AS A_ACCT, 
		dbo.Alert.Cust AS A_CUST, 
		dbo.Alert.WLCode AS A_WLCODE,
		dbo.Watchlist.Title, 
		dbo.Alert.[Desc] AS A_DESC, 
		dbo.Alert.Status AS A_STATUS, 
		dbo.Alert.CreateDate AS A_CREATE_DATE, 
		dbo.Alert.LastOper AS A_LASTOPER, 
		dbo.Alert.LastModify AS A_LAST_MODIFIED, 
		dbo.Alert.ReviewState AS A_REVIEW_STATE, 
		dbo.Alert.ReviewOper AS A_REVIEW_OPER, 
		dbo.Alert.ReviewTime AS A_REVIEW_TIME, 
		dbo.Alert.App AS A_APPROVE, 
		dbo.Alert.AppOper AS A_APP_OPER, 
		dbo.Alert.AppTime AS A_APP_TIME, 
		dbo.Alert.Annotation AS A_ANNOTATION, 
		Customer.Id AS A_CUST, 
		Customer.Name AS A_CUST_NAME
	FROM 
		dbo.Alert 
	INNER JOIN
		dbo.Watchlist 
		ON dbo.Alert.WLCode = dbo.Watchlist.WLCode 
	left outer JOIN
		dbo.Customer 
		ON dbo.Alert.Cust = dbo.Customer.Id
END

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-22 : 12:52:57

quote:

Originally posted by Lamprey

try something like:

@Start_Date datetime,
@End_date datetime,
@History varchar(1))
AS

IF  @history = 'y' 
BEGIN
	SELECT 
		dbo.AlertHist.AlertNo AS AH_ALERTNO, 
		dbo.AlertHist.Account AS AH_ACCT, 
		dbo.AlertHist.Cust AS AH_CUST_ID, 
		dbo.Customer.Id AS AH_CUSTID, 
		dbo.Customer.Name AS AH_CUSTNAME, 
		dbo.AlertHist.WLCode AS AH_WLCODE, 
		dbo.AlertHist.[Desc] AS AH_DESC,
		dbo.AlertHist.Status AS AH_STATUS, 
		dbo.AlertHist.CreateDate AS AH_CREATE_DATE, 
		dbo.AlertHist.LastOper AS AH_LASTOPER, 
		dbo.AlertHist.LastModify AS AH_LASTMODIFY, 
		dbo.AlertHist.ReviewState AS AH_REVIEW_STATE, 
		dbo.AlertHist.ReviewOper AS AH_REVIEW_OPER, 
		dbo.AlertHist.ReviewTime AS AH_REVIEW_TIME, 
		dbo.AlertHist.App AS AH_APPROVE, 
		dbo.AlertHist.AppTime AS AH_APPROVE_TIME, 
		dbo.AlertHist.AppOper AS AH_APPOPER, 
		dbo.AlertHist.Annotation AS AH_ANNOTATION
	FROM
		-- Insert Table(s) Here
END
ELSE
BEGIN
	SELECT
		dbo.Alert.AlertNo AS A_ALERTNO, 
		dbo.Alert.Account AS A_ACCT, 
		dbo.Alert.Cust AS A_CUST, 
		dbo.Alert.WLCode AS A_WLCODE,
		dbo.Watchlist.Title, 
		dbo.Alert.[Desc] AS A_DESC, 
		dbo.Alert.Status AS A_STATUS, 
		dbo.Alert.CreateDate AS A_CREATE_DATE, 
		dbo.Alert.LastOper AS A_LASTOPER, 
		dbo.Alert.LastModify AS A_LAST_MODIFIED, 
		dbo.Alert.ReviewState AS A_REVIEW_STATE, 
		dbo.Alert.ReviewOper AS A_REVIEW_OPER, 
		dbo.Alert.ReviewTime AS A_REVIEW_TIME, 
		dbo.Alert.App AS A_APPROVE, 
		dbo.Alert.AppOper AS A_APP_OPER, 
		dbo.Alert.AppTime AS A_APP_TIME, 
		dbo.Alert.Annotation AS A_ANNOTATION, 
		Customer.Id AS A_CUST, 
		Customer.Name AS A_CUST_NAME
	FROM 
		dbo.Alert 
	INNER JOIN
		dbo.Watchlist 
		ON dbo.Alert.WLCode = dbo.Watchlist.WLCode 
	left outer JOIN
		dbo.Customer 
		ON dbo.Alert.Cust = dbo.Customer.Id
END

Haha, that's what I meaned but I was too lazy to write it down

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources