how can i return value from exec

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)

how can i return value from exec

Author

Topic

m.abdullah
Starting Member

7 Posts

Posted - 2007-12-02 : 11:57:48

iam new to MS sql 2000 so i have a stored procedure that making paging for me and i want to return the count of the records as out put parameter with the results

the stored procedure

CREATE PROCEDURE [dbo].[GetProducts]
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int,

	@ItemCount int output   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				Create Table #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [ProductID] int 
				)
				
				-- Insert into the temp table
				declare @SQL as nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex (ProductID)'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [ProductID]'
				SET @SQL = @SQL + ' FROM dbo.[Products]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				exec sp_executesql @SQL

				-- Return paged results
				SELECT O.[ProductID], O.[ProductName]
				FROM
				    dbo.[Products] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexID > @PageLowerBound
					AND O.[ProductID] = PageIndex.[ProductID]
				ORDER BY
				    PageIndex.IndexID
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
				SET @SQL = @SQL + ' FROM dbo.[Products]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				exec sp_executesql @SQL
			
				END
			



GO

so what i need is
set @ItemCount = exec sp_executesql @SQL
or something like that anyone can help me

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-02 : 12:08:03

you better read this http://www.sommarskog.se/dynamic_sql.html

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-03 : 01:43:38

or http://www.nigelrivett.net/SQLTsql/sp_executesql.html

Madhivanan

Failing to plan is Planning to fail

m.abdullah
Starting Member

7 Posts

Posted - 2007-12-03 : 03:34:44

thanks to you i am new to dynamic sql so i found these articles are very useful

thanks again

and now it's working

CREATE PROCEDURE [dbo].[GetProducts]
(

	@WhereClause varchar (2000) = Null ,

	@OrderBy varchar (2000) = Null ,

	@PageIndex int   ,

	@PageSize int,

 	@ItemCount   int output

 
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				/*DECLARE @paramList  nvarchar(1000)*/
				DECLARE @RtnCount   int 

				
				-- Set the page bounds
				SET @PageLowerBound = (@PageIndex - 1) * @PageSize
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				Create Table #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [ProductID] int 
				)
				
				-- Insert into the temp table
				declare @SQL as nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex (ProductID)'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [ProductID]'
				SET @SQL = @SQL + ' FROM dbo.[Products]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				exec sp_executesql @SQL

				-- Return paged results
				SELECT O.[ProductID], O.[ProductName]
				FROM
				    dbo.[Products] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexID > @PageLowerBound
					AND O.[ProductID] = PageIndex.[ProductID]
				ORDER BY
				    PageIndex.IndexID
				
				-- get row count

				SET @SQL = 'SELECT @RtnCount = COUNT(ProductID)'
				SET @SQL = @SQL + ' FROM dbo.[Products]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END


				EXEC SP_EXECUTESQL @SQL, N'@WhereClause varchar(2000),@RtnCount int OUTPUT', @WhereClause, @RtnCount  = @ItemCount OUTPUT
			
				END
GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-03 : 09:00:47

Did you read about the SQL Injection in the link ?

KH
[spoiler]Time is always against us[/spoiler]

m.abdullah
Starting Member

7 Posts

Posted - 2007-12-05 : 07:59:16

honestly no

if you found anything bad in code please tell me thanks and by the way i pass thease parameters from a listboxes so there are no inputs from the users

and thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources