August 10, 2003 at 7:32 am
Can anyone help me with converting the following stored procedure into a dynamic stored procedure:
ALTER Procedure ExportLogin
(
@UserName nvarchar(50),
@user-idnvarchar(6) OUTPUT
)
AS
SELECT
@user-id = ExporterID
FROM
tbl_Exporter
WHERE
UserName = @UserName
IF @@Rowcount < 1
SELECT
@user-id = 0
The dynamic part would be changing the table and also changing the select clause.
All help will be much appreciated.
thanks!
August 10, 2003 at 3:02 pm
In what way will the SELECT clause change? Just the column name and where criterion? Will it always be just the 1 column selected, and just 1 column in the WHERE clause? Is the table name to be passed to the stored procedure? If so, will the SELECTed column name and WHERE column name also be passed, or will the stored procedure determine the column names itself based on the table name?
Sorry for all the questions, but some clarification will make it easier to help.
Note, when the stored procedure makes use of dynamic sql then just granting EXECUTE authority will not be enough. Anyone wanting to use the stored procedure will need SELECT authority on the table/s.
Cheers,
- Mark
Cheers,
- Mark
August 10, 2003 at 10:44 pm
Hi
your mean something like this below, where @p_dbname is a parameter to the stored proc.
set @Nsqlstring = '
select @vfileid = fileid,
@vtotalpages = size - 1
from ' + @p_dbname + '..sysfiles
where fileid = ' + cast(@p_fileid as varchar)
EXEC sp_executesql @Nsqlstring, N'@vfileid integer OUTPUT, @vtotalpages integer OUTPUT',
@fileid OUTPUT,
@totalpages OUTPUT
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply