T-SQL help - 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

SQL Server 2000 Forums

Transact-SQL (2000)

T-SQL help

Author

Topic

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2005-06-23 : 19:28:32

Here is my SP


create procedure proc1
AS
select 'abc','xyz'

so it returns


abc  xyz

if want to modify it to something like this


create procedure proc1
(
@loop as int
)
AS
select 'abc','xyz'

if i pass 3 to @loop
it should give me


abc  xyz
abc  xyz
abc  xyz

i dont want to use loop.
is there any other way?

Thanks for help

mk_garg

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-23 : 19:31:30

Numbers is a Tally table with one column, n (integer), with rows 1-8000.

declare @n int
set @n = 3

select Column1, Column2
from (select 'abc' as Column1, 'xyz' as Column2) t
cross join numbers
where n between 1 and @n

Tara

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2005-06-23 : 19:37:02

Thanks that is good advise.

Any other alternate beside creating Numbers table in db.
and one more thing to mention, I am using SQL server 7.0.

Thanks

mk_garg

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-23 : 19:39:07

I certainly can't think of another way. Crossing joining the numbers table is a fast solution. I think any other solution will require looping.

Tara

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2005-06-23 : 19:44:51

Here is stored procedure.


CREATE PROCEDURE [dbo].sp_Get_StockLabel
(
@StockCode AS VARCHAR(10) =  '',
@Deleted AS SMALLINT = -1, 
@ClientID AS VARCHAR(10) = '',
@Loop AS INTEGER = 1
) 
AS

SET NOCOUNT ON
SET XACT_ABORT ON

DECLARE @strQuery AS VARCHAR(1500)

IF LEN(@StockCode) = 0
	SET @StockCode = NULL
IF @Deleted = -1
	SET @Deleted = NULL
IF LEN(@ClientID) = 0
	SET @ClientID =NULL

/* Get stock details */
SELECT 	Stock.StockCode, 
	Stock.StockName,
	Stock.StockType,
	StockCode3of9 = '*' + Stock.StockCode + '*',
	ISNULL(Stock.ClientID,'') AS ClientID,
	ISNULL(Client.ClientName,'') AS ClientName,
	ISNULL(Stock.SupplierID,'') AS SupplierID,
	ISNULL(Supplier.SupplierName,'') AS SupplierName,
	Stock.ReferenceCode,
	Stock.ClientStockCode INTO #Stock
FROM Stock 
LEFT JOIN Client ON Stock.ClientID = Client.ClientID
LEFT JOIN Supplier ON Stock.SupplierID = Supplier.SupplierID
WHERE StockCode=COALESCE(@StockCode,StockCode)
	AND Stock.ClientID=COALESCE(@ClientID,Stock.ClientID)
	AND Stock.Deleted=COALESCE(@Deleted,Stock.ClientID)
ORDER BY Stock.StockCode

DECLARE @LoopCount AS Integer
SET @LoopCount = 1

WHILE @LoopCount < @Loop
BEGIN
	INSERT INTO #Stock (StockCode, StockName, StockType, StockCode3of9, ClientID, ClientName, SupplierID, SupplierName, ReferenceCode, ClientStockCode)
	SELECT 	Stock.StockCode, 
		Stock.StockName,
		Stock.StockType,
		StockCode3of9 = '*' +Stock.StockCode + '*',
		ISNULL(Stock.ClientID,'') AS ClientID,
		ISNULL(Client.ClientName,'') AS ClientName,
		ISNULL(Stock.SupplierID,'') AS SupplierID,
		ISNULL(Supplier.SupplierName,'') AS SupplierName,
		Stock.ReferenceCode,
		Stock.ClientStockCode
	FROM Stock LEFT JOIN Client
	ON Stock.ClientID = Client.ClientID
	LEFT JOIN Supplier
	ON Stock.SupplierID = Supplier.SupplierID
	WHERE StockCode=COALESCE(@StockCode,StockCode)
	AND Stock.ClientID=COALESCE(@ClientID,Stock.ClientID)
	AND Stock.Deleted=COALESCE(@Deleted,Stock.ClientID)
	ORDER BY Stock.StockCode
	SET @LoopCount = @LoopCount + 1
END

--EXEC (@strQuery)
--PRINT @strQuery1
SELECT UPPER(StockCode) as StockCode, StockName, StockType, UPPER(StockCode3of9) as StockCode3of9, ClientID, ClientName, SupplierID, SupplierName, ReferenceCode, UPPER(ClientStockCode) as ClientStockCode FROM #Stock


SET NOCOUNT OFF
SET XACT_ABORT OFF
GO

I want to drop temp table to stop recompiling stored procedure.

Thanks

mk_garg

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-24 : 12:17:20

I'm not sure what we can do with your stored procedure without DDL of all tables, DML for sample data, and the expected result set.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources