Need help with stored proc - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Need help with stored proc

Author

Topic

Movva
Starting Member

15 Posts

Posted - 2011-05-24 : 16:13:36

Hi ,

I am writing a stored proc and have parameter @Firsthundred bit. Based on the value (either 1 or 0) I need to return first 100 records or all records.

So I am trying with

Create stored procedure [dbo].[ShowResults]
@FirstHundred bit
Begin
If (@FirstHundred)
SELECT TOP 100
ELSE
SELECT DISTINCT

and then my all the columns goes here..... I guess may be this is called dynamic sql but not sure though.

Can some one throw some light.

Thanks,

L

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-24 : 16:18:53

If @FirstHundred = 1
SELECT TOP 100 * from tbl order by ordercol
ELSE
SELECT * from tbl

Note you need an orderby for "first" to be meaningful - otherwise it would be "any"

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-05-24 : 16:35:57

Here are some other alternatives:

DECLARE @FirstHundred BIT
DECLARE @RowsToReturn BIGINT

SET @FirstHundred = 1

--------------------------------------------
-- Using TOP
--------------------------------------------
IF @FirstHundred = 1
    SET @RowsToReturn = 100
ELSE
    SET @RowsToReturn = 9223372036854775807


SELECT TOP (@RowsToReturn) *
FROM dbo.MyTable


--------------------------------------------
-- Using ROWCOUNT
--------------------------------------------
IF @FirstHundred = 1
    SET @RowsToReturn = 100
ELSE
    SET @RowsToReturn = 0

SET ROWCOUNT @RowsToReturn

SELECT *
FROM dbo.MyTable

SET ROWCOUNT 0

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-05-24 : 21:16:08

>> I am writing a stored proc and have parameter @Firsthundred BIT. Based on the value (either 1 or 0) I need to return first 100 records [sic] or all records [sic].

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

Your approach to SQL is fundamentally wrong . We do not use BIT flags; that was assembly languages, Rows are not records. Tables have no ordering; they are sets. That was sequential tape files.

Your skeleton code assumes that your BIT flag is a Boolean; it is a numeric in SQL and can be NULL. Instead of this silly bit flag, tell us how many rows you want to see and what the ordering is:

CREATE PROCEDURE ShowFirstFoobars
(@in_first_cnt INTEGER )

SELECT *
FROM (SELECT Foobar.*, ROW_NUMBER () OVER (ORDER BY vague_sort_column) AS rn
FROM Foobar) AS X
WHERE rn <= @in_result_cnt;

There is no need to mimic a mag tape, use proprietary T^OP oir dynamic SQL.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources