SQL Stored Procedure with parameters

Question

SQL Stored Procedure with parameters

b_boy

SSCertifiable

Points: 5145
More actions
July 25, 2008 at 8:45 am

#196031

I am using the following query to search for a result but it doesn't seem to be working, can anyone help me out?
CREATE PROCEDURE OrderID
@OrderID NVARCHAR (40)
AS
SELECTT_OrderHeader.OrderID,T_OrderHeader.CustomerID,T_OrderHeader.ShipCountry, T_ICPAudit.ID, CONVERT (MONEY, (T_ICPAudit.Amount)) AS Amount,
T_ICPAudit.TransactionType,T_ICPAudit.TransactionResult,T_ICPAudit.ICPBatchNumber,
CONVERT (NVARCHAR (20), (T_ICPAudit.RecordCreated), 107) AS [RecordCreated],
T_ICPAudit.MerchantNumber, T_ICPAudit.SchemeName
FROM T_OrderHeader
INNER JOIN T_Payments ON T_OrderHeader.OrderID = T_Payments.OrderID
INNER JOINT_ICPAudit ON T_Payments.CCNumber = T_ICPAudit.CardNumber COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE (RecordCreated BETWEEN (@StartDate) AND (@EndDate))
OR (T_OrderHeader.OrderID = @OrderID)
OR (T_OrderHeader.CustomerID = @CustomerID)
OR ( T_ICPAudit.ID = @ICPAuditID)
GO

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

b_boy, you haven't declared all of the variables. In any case, it's going to save you time to test this as a query in QA before comitting it to a sp. Use something like this. It's going to need more work on the WHERE clause to make it work nice in the sp.

DECLARE @OrderID NVARCHAR (40), @StartDate DATETIME, @EndDate DATETIME, @CustomerID NVARCHAR (40)

SET @OrderID = something

SET @StartDate = something

SET @EndDate = something

SET @CustomerID = something

SELECT T_OrderHeader.OrderID,T_OrderHeader.CustomerID,T_OrderHeader.ShipCountry, T_ICPAudit.ID, CONVERT (MONEY, (T_ICPAudit.Amount)) AS Amount,

T_ICPAudit.TransactionType,T_ICPAudit.TransactionResult,T_ICPAudit.ICPBatchNumber,

CONVERT (NVARCHAR (20), (T_ICPAudit.RecordCreated), 107) AS [RecordCreated],

T_ICPAudit.MerchantNumber, T_ICPAudit.SchemeName

FROM T_OrderHeader

INNER JOIN T_Payments ON T_OrderHeader.OrderID = T_Payments.OrderID

INNER JOIN T_ICPAudit ON T_Payments.CCNumber = T_ICPAudit.CardNumber COLLATE SQL_Latin1_General_CP1_CI_AS

WHERE (RecordCreated BETWEEN (@StartDate) AND (@EndDate))

OR (T_OrderHeader.OrderID = @OrderID)

OR (T_OrderHeader.CustomerID = @CustomerID)

OR ( T_ICPAudit.ID = @ICPAuditID)

BTW if you use table aliases, it will make your query far more readable - look it up in BOL.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mtassin SSC-Insane Points: 23099 More actions · Answer 2

Please provide appropriate create table, data insert, etc queries so that folks can troubleshoot this. Also, please state what the desired outcome as well as the current outcome are.

Thanks,

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

First of all the code you posted only has one parameter defined and you reference 5 in the query so where are they coming from?

Here are a few things to consider:

1. Be careful using BETWEEN with dates because SQL Server DateTime fields are Date and Time so '1/1/2008' is '1/1/2008 00:00:00' so if I use BETWEEN '1/1/2008' and '1/31/2008' I will not get any data for '1/31/2008'.

2. Currently your query will return any data between the dates OR data that meets one of the other criteria. Are you sure that is what you want? Normally you want to limit by date AND other criteria.

3. Are there any instances where one of the columns you join on can be NULL? If so your join on that needs to be an OUTER JOIN.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

b_boy SSCertifiable Points: 5145 More actions · Answer 4

The query is to be used for a report within SSRS, but before i commit it into SSRS, i need to get the query right.

The outcome of the query is that, when a user runs a report, the first and second parameters which are dates is a compulsory selection (This done within SSRS), the remaining three parameters, are option if the users wants to narrow down his/her search., based on OrderID, CustomerID or CPAuditID

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

I think this is what you are looking for, keeping in mind my other comments:

[font="Courier New"]CREATE PROCEDURE OrderID

@StartDate DATETIME,

@EndDate DATETIME,

@OrderID NVARCHAR (40) = NULL,

@CustomerId NVARCHAR(40) = NULL,

@ICPAuditID NVARCHAR(40) = NULL

AS

SELECT

T_OrderHeader.OrderID,

T_OrderHeader.CustomerID,

T_OrderHeader.ShipCountry,

T_ICPAudit.ID,

CONVERT (MONEY, (T_ICPAudit.Amount)) AS Amount,

T_ICPAudit.TransactionType,

T_ICPAudit.TransactionResult,

T_ICPAudit.ICPBatchNumber,

CONVERT (NVARCHAR (20), (T_ICPAudit.RecordCreated), 107) AS [RecordCreated],

T_ICPAudit.MerchantNumber, T_ICPAudit.SchemeName

FROM

T_OrderHeader INNER JOIN

T_Payments ON

T_OrderHeader.OrderID = T_Payments.OrderID INNER JOIN

T_ICPAudit ON

T_Payments.CCNumber = T_ICPAudit.CardNumber COLLATE SQL_Latin1_General_CP1_CI_AS

WHERE

(RecordCreated BETWEEN (@StartDate) AND (@EndDate)) AND

(T_OrderHeader.OrderID = CASE WHEN @OrderId IS NULL THEN T_OrderHeader.OrderID ELSE @OrderID END) AND

(T_OrderHeader.CustomerID = CASE WHEN @CustomerID IS NULL THEN T_OrderHeader.CustomerID ELSE @CustomerID END) AND

(T_ICPAudit.ID = CASE WHEN @ICPAuditID IS NULL THEN T_OrderHeader.ICPAuditID ELSE @ICPAuditID END)

GO[/font]

Or'ing the criteria with NULL variables does not work because you need to AND in order to be additive and NULL does not equal anything.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

So, you're starting about here...

DECLARE @StartDate DATETIME, @EndDate DATETIME, @OrderID NVARCHAR (40), @CustomerID NVARCHAR (40), @ICPAuditID NVARCHAR (40)

SET @StartDate = somedatetime

SET @EndDate = somedatetime

SET @OrderID = something or NULL

SET @CustomerID = something or NULL

SET @ICPAuditID = something or NULL -- <-- thanks Jack

SELECT h.OrderID, h.CustomerID, h.ShipCountry, a.[ID],

CONVERT (MONEY, a.Amount) AS Amount,

a.TransactionType, a.TransactionResult, a.ICPBatchNumber,

CONVERT (NVARCHAR (20), a.RecordCreated, 107) AS [RecordCreated],

a.MerchantNumber, a.SchemeName

FROM T_OrderHeader h

INNER JOIN T_Payments p ON h.OrderID = p.OrderID

INNER JOIN T_ICPAudit a ON p.CCNumber = a.CardNumber COLLATE SQL_Latin1_General_CP1_CI_AS

WHERE RecordCreated BETWEEN @StartDate AND @EndDate

AND (

h.OrderID = @OrderID

OR h.CustomerID = @CustomerID

OR a.[ID] = @ICPAuditID

)

Edit: Nope, Jack's done it all for ya. Happy friday! 😀

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Venkatesan Prabu SSCommitted Points: 1892 More actions · Answer 7

Post the error to use, Please try out with this query

CREATE PROCEDURE OrderID

@OrderID NVARCHAR (40)

BEGIN

AS

SELECT T_OrderHeader.OrderID,T_OrderHeader.CustomerID,T_OrderHeader.ShipCountry, T_ICPAudit.ID,

CONVERT (MONEY, (T_ICPAudit.Amount)) AS Amount,

T_ICPAudit.TransactionType,T_ICPAudit.TransactionResult,T_ICPAudit.ICPBatchNumber,

CONVERT (NVARCHAR (20), (T_ICPAudit.RecordCreated), 107) AS [RecordCreated],

T_ICPAudit.MerchantNumber, T_ICPAudit.SchemeName

FROM T_OrderHeader

INNER JOIN T_Payments ON T_OrderHeader.OrderID = T_Payments.OrderID

INNER JOIN T_ICPAudit ON T_Payments.CCNumber = T_ICPAudit.CardNumber COLLATE SQL_Latin1_General_CP1_CI_AS

WHERE (RecordCreated BETWEEN (@StartDate) AND (@EndDate))

OR (T_OrderHeader.OrderID = @OrderID)

OR (T_OrderHeader.CustomerID = @CustomerID)

OR (T_ICPAudit.ID = @ICPAuditID)

END

GO

Thanks and Regards,
Venkatesan Prabu, 😛
My Blog:

http://venkattechnicalblog.blogspot.com/