August 30, 2010 at 5:37 am
Hi folks, long time reader first time poster 🙂
I wonder if anyone can help or advise
What I am trying to do is read in a set of procedures which are a column in a table and pass it a parameter which would be another field in that table. It will be a different parameter for most reports
This is what I am trying to achieve (in its basic form)
--TEST TABLE
CREATE TABLE [dbo].[Test](
[query] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[parameter] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
--TEST DATA
INSERT INTO TEST
SELECT 'REMOTESERVER.TEST1.DBO.TESTPROC1','24 Aug 2010'
UNION
SELECT 'REMOTESERVER.TEST1.DBO.TESTPROC2','28 Aug 2010'
--CURSOR
DECLARE @SQLCMD NVARCHAR(MAX)
DECLARE @QUERY VARCHAR(100)
DECLARE @PARAMETER VARCHAR(100)
DECLARE CUR_GENREPORTS CURSOR FOR
SELECT
QUERY,
PARAMETER
FROM TEST
FOR READ ONLY
OPEN CUR_GENREPORTS
FETCH CUR_GENREPORTS INTO
@QUERY,@PARAMETER
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLCMD = 'EXEC ' + @QUERY + ' ' + CHAR(39) + @PARAMETER + CHAR(39)
EXECUTE SP_EXECUTESQL @SQLCMD
--PRINT @SQLCMD
FETCH CUR_GENREPORTS INTO
@QUERY,@PARAMETER
END
CLOSE CUR_GENREPORTS
DEALLOCATE CUR_GENREPORTS
This works fine and returns the results of
EXEC REMOTESERVER.TEST1.DBO.TESTPROC1 '24 Aug 2010'
EXEC REMOTESERVER.TEST1.DBO.TESTPROC2 '28 Aug 2010'
The bit I'm stuck on is that I need the paramaters to be more dynamic eg
INSERT INTO TEST
SELECT 'REMOTESERVER.TEST1.DBO.TESTPROC1',getdate()
UNION
SELECT 'REMOTESERVER.TEST1.DBO.TESTPROC2',getdate()-30
Returning....
EXEC REMOTESERVER.TEST1.DBO.TESTPROC1 getdate()
EXEC REMOTESERVER.TEST1.DBO.TESTPROC2 getdate()-30
....which I understand doesnt work. Is it possible for me to work the problem this way? I've tried converting the values to datetime but get conversion errors and am wondering if the way I am trying to do it is achievable
Advice very much appreciated
Thanks
August 30, 2010 at 6:02 am
it sounds like you are trying to make one generic proc , instead of multiple procs for specific purposes.
in your table definition, your parameter column is a varchar field, but you are using it as a datetime...
you should not switch back and forth between datatypes, or you can get conversion errors just like you are getting here.
it would be better to pass datetime for datetime values, int for itn, etc, to avoid implicit conversions.
if you stay with that design, you need to put the datetime into a varchar format that is universal:
'2010-08-30 08:00:06.790'
SELECT CONVERT(VARCHAR(40),getdate(),121)
Lowell
August 30, 2010 at 6:18 am
Lowell thanks for your reply
Sorry maybe I can be more specific
I can set the columns to datetime though I need the parameter stored in that field to be variable, ie different for each report, some may be weekly reports/daily etc so if it was date time I cant for example put the value "getdate()" in it to pass to the stored proc or have a default on the column . I want that to kind of be an expression that I can change for each report and append to the end of the stored proc
And yes I want this to be a single proc that we can just add new reports and its default parameters quickly
Hope that makes sense. Please let me know if I can provide more info or if anything is unclear
August 30, 2010 at 6:26 am
matthew.wilson (8/30/2010)
Lowell thanks for your replySorry maybe I can be more specific
I can set the columns to datetime though I need the parameter stored in that field to be variable, ie different for each report, some may be weekly reports/daily etc so if it was date time I cant for example put the value "getdate()" in it to pass to the stored proc or have a default on the column . I want that to kind of be an expression that I can change for each report and append to the end of the stored proc
And yes I want this to be a single proc that we can just add new reports and its default parameters quickly
Hope that makes sense. Please let me know if I can provide more info or if anything is unclear
i htink you'll need to tweak the procedure that will be called; something like this:
CREATE PROCEDURE TESTPROC @PARAM VARCHAR(50)
AS
BEGIN
DECLARE @DATE datetime
IF PARAM ='getdate()'
SET @DATE = getdate()
IF ISDATE(@PARAM) = 1
SET @DATE = @PARAM
--if it's not a date and not the right string, it will be null at this point...
...
END
Lowell
August 30, 2010 at 6:38 am
ah so simple 🙂
Thanks very much for your help that does exactly what I want
DECLARE @SQLCMD NVARCHAR(MAX)
DECLARE @QUERY VARCHAR(100)
DECLARE @PARAMETER VARCHAR(100)
DECLARE CUR_GENREPORTS CURSOR FOR
SELECT
QUERY,
PARAMETER
FROM TEST
FOR READ ONLY
OPEN CUR_GENREPORTS
FETCH CUR_GENREPORTS INTO
@QUERY,@PARAMETER
WHILE @@FETCH_STATUS = 0
BEGIN
IF @PARAMETER ='getdate()'
SET @PARAMETER = convert(varchar,getdate(),112)
IF @PARAMETER ='getdate()-2'
SET @PARAMETER = convert(varchar,getdate()-2,112)
SET @SQLCMD = 'EXEC ' + @QUERY + ' ' + CHAR(39) + @PARAMETER + CHAR(39)
--EXECUTE SP_EXECUTESQL @SQLCMD
PRINT @SQLCMD
FETCH CUR_GENREPORTS INTO
@QUERY,@PARAMETER
END
CLOSE CUR_GENREPORTS
DEALLOCATE CUR_GENREPORTS
This gives me the output I am after
EXEC REMOTESERVER.TEST1.DBO.TESTPROC1 '20100828'
EXEC REMOTESERVER.TEST1.DBO.TESTPROC2 '20100830'
Thanks a lot
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply