Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Dynamic "where" statement (Stored procedure)

Posted on 2008-06-13
2
Medium Priority
?
363 Views
Last Modified: 2008-06-13
Se attached code snippet. I need help to make the "whereStatement" correct.

If @guid is NOT blank I want the where statement to be like this:
where active='1'  and guid=@guid

If @guid is blank I want the where statement to be like this:
where active='1'  and codeID=@codeID

Thanks for all help :)
ALTER PROCEDURE [dbo].[spKodeDetaljPay] 
(
@codeid smallint,
@guid varchar(64)
)
AS
 
BEGIN
 
Select title, text, 
(Select name from tblPersonPay where tblCode.PersonID = tblPersonPay.PersonID) as Name,
(Select email from tblPersonPay where tblCode.PersonID = tblPersonPay.PersonID) as email from tblCode
where active='1' -- and " & whereStatement 
 
END

Open in new window

0
Comment
Question by:webressurs
2 Comments
 
LVL 6

Accepted Solution

by:
Compaq_Engineer earned 500 total points
ID: 21779054
Try;-
ALTER PROCEDURE [dbo].[spKodeDetaljPay]
(
@codeid smallint,
@guid varchar(64)
)
AS
 
BEGIN
 
Select title, text,
(Select name from tblPersonPay where tblCode.PersonID = tblPersonPay.PersonID) as Name,
(Select email from tblPersonPay where tblCode.PersonID = tblPersonPay.PersonID) as email from tblCode
where
(active='1' and guid=@guid)
OR
(active='1' and codeid=@codeid and @guid='')
 
END
0
 
LVL 16

Assisted Solution

by:brad2575
brad2575 earned 500 total points
ID: 21779089
have to set up a query into a string and execute the string

DECLARE @query as varchar(MAX)
 
Set @query = 'Select title, text, ' +
	'(Select name from tblPersonPay where tblCode.PersonID = tblPersonPay.PersonID) as Name, ' +
	'(Select email from tblPersonPay where tblCode.PersonID = tblPersonPay.PersonID) as email from tblCode ' +
	'where active=''1''  '
 
IF @guid = '' OR @guid IS NULL
    Set @query = @query + 'AND guid=''' + @guid + ''' '
ELSE 
	Set @query = @query + 'AND codeID=''' + @codeID  + ''' '
 
EXEC(@query)

Open in new window

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question