Solved

Sql query to Stored Procedure

Posted on 2016-11-29
6
44 Views
Last Modified: 2016-11-29
Hello,

I have a query need to convert it to SP:

Any suggestions:


SELECT TOP 1 CASE WHEN cnt = cnt1 THEN 1 ELSE 0 END IsCheckedAll  
FROM (
      SELECT * , COUNT(*) OVER () cnt , COUNT(Checked) OVER () cnt1 FROM PRXTRACT
      WHERE InvoiceNumber =@InvoiceNumber
)k

Open in new window

0
Comment
Question by:RIAS
6 Comments
 
LVL 47

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 41905601
Basically write the CREATE PROCEDURE header with the necessary parameter definition and copy the query into the SP:
CREATE PROCEDURE  usp_Name
   @InvoiceNumber INT      
AS
BEGIN
	SELECT TOP 1 CASE WHEN cnt = cnt1 THEN 1 ELSE 0 END IsCheckedAll  
	FROM (
      SELECT * , COUNT(*) OVER () cnt , COUNT(Checked) OVER () cnt1 FROM PRXTRACT
      WHERE InvoiceNumber =@InvoiceNumber
		) k

END

Open in new window

0
 
LVL 28

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 250 total points
ID: 41905603
Try.. choose Datatype based on your column name, Are you expecting NULL also in @InvoiceNumber ?

CREATE PROC YourProcName
(
	@InvoiceNumber VARCHAR(100) 
)
AS
BEGIN


SELECT TOP 1 CASE WHEN cnt = cnt1 THEN 1 ELSE 0 END IsCheckedAll  
FROM (
      SELECT * , COUNT(*) OVER () cnt , COUNT(Checked) OVER () cnt1 FROM PRXTRACT
      WHERE InvoiceNumber =@InvoiceNumber
)k


END

Open in new window

0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41905606
Got it. It is Varchar only. Its the same query I wrote yesterday. :)
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:RIAS
ID: 41905619
Experts,Both the solution are same except datatype.
Will split points equally.
0
 
LVL 33

Expert Comment

by:ste5an
ID: 41905631
This should be faster:

IF EXISTS ( SELECT  *
            FROM    PRXTRACT
            WHERE   InvoiceNumber = @InvoiceNumber
                    AND Checked IS NULL )
    SELECT  0 AS IsCheckedAll;    
ELSE
    SELECT  1 AS IsCheckedAll;

Open in new window

1
 

Author Comment

by:RIAS
ID: 41905680
Thanks Stefan!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSIS with VPN COnnection 2 77
always on switch back after failover 2 35
Unable to Uninstall Visual Studio 2015 7 27
Passing Parameter to Stored Procedure 4 24
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

803 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