Solved

Sql query to Stored Procedure

Posted on 2016-11-29
6
14 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 45

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 17

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal 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 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41905606
Got it. It is Varchar only. Its the same query I wrote yesterday. :)
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

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

Expert Comment

by:Stefan Hoffmann
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.

Join & Write a Comment

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

708 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now