April 15, 2015 at 3:48 am
PASSING PARAMETERS (not my father :w00t:)
create proc Parameter_Provider as
declare @login varchar(4) = 'BWFC'
declare @results table
(
col1 varchar(10)
,col2 int
)
execute sp_executesql Parameter_Recipient ---requires @login parameter.
--- I know this syntax isn't correct but I don't know what is!!!
insert into @results
select col1, col2 from dbo.Table_Populated_By_Parameter_Recipient
/*The proc that uses @results goes here*/
I'm struggling to pass a parameter from one proc to another. The script I'm creating can be greatly simplified by using the results of a stored proc that's been in place for some time. It seems to me that it should be reasonably simple to pass a parameter from my script into the existing one but I'm really struggling. Is it actually possible and if so can somebody point me at a good article on how to do it please?
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
April 15, 2015 at 4:01 am
BWFC (4/15/2015)
PASSING PARAMETERS (not my father :w00t:)
create proc Parameter_Provider asdeclare @login varchar(4) = 'BWFC'
declare @results table
(
col1 varchar(10)
,col2 int
)
execute sp_executesql Parameter_Recipient ---requires @login parameter.
--- I know this syntax isn't correct but I don't know what is!!!
insert into @results
select col1, col2 from dbo.Table_Populated_By_Parameter_Recipient
/*The proc that uses @results goes here*/
I'm struggling to pass a parameter from one proc to another. The script I'm creating can be greatly simplified by using the results of a stored proc that's been in place for some time. It seems to me that it should be reasonably simple to pass a parameter from my script into the existing one but I'm really struggling. Is it actually possible and if so can somebody point me at a good article on how to do it please?
Are you just trying to do: -
INSERT INTO @results
EXECUTE Parameter_Recipient @login;
??
April 15, 2015 at 4:08 am
Cadavre (4/15/2015)
Are you just trying to do: -
INSERT INTO @resultsEXECUTE Parameter_Recipient @login;
??
Basically yes. I want to take the results from Parameter_Recipient, when run for a given login (it will take other parameters but I just want to get it to work for one first) and use them in my proc.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
April 15, 2015 at 4:29 am
I may be misunderstanding you, but does this help?
Setup -
IF OBJECT_ID('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;
SELECT TOP 5
IDENTITY( INT,1,1 ) AS ID,
RAND(CHECKSUM(NEWID())) * 30000 /*(Number of days in range)*/
+ CAST('1945' AS DATETIME) /*(Start date, e.g. '1945-01-01 00:00:00*/ AS randomDateTime,
DATEADD(DAY,
( ( ABS(CHECKSUM(NEWID())) % 366 /*(Number of days in range)*/ )
+ 1 ), CAST('1945' AS DATE)/*(Start date, e.g. '1945-01-01*/) AS randomDate,
ABS(CHECKSUM(NEWID())) AS randomBigInt,
( ABS(CHECKSUM(NEWID())) % 100 ) + 1 AS randomSmallInt,
RAND(CHECKSUM(NEWID())) * 100 AS randomSmallDec,
RAND(CHECKSUM(NEWID())) AS randomTinyDec,
RAND(CHECKSUM(NEWID())) * 100000 AS randomBigDec,
CONVERT(VARCHAR(6), CONVERT(MONEY, RAND(CHECKSUM(NEWID())) * 100), 0) AS randomMoney,
CRYPT_GEN_RANDOM(1) % 2 AS randomBool
INTO #testEnvironment
FROM master.dbo.syscolumns sc1
CROSS JOIN master.dbo.syscolumns sc2
CROSS JOIN master.dbo.syscolumns sc3;
IF EXISTS ( SELECT 1
FROM sysobjects
WHERE id = OBJECT_ID('Parameter_1')
AND OBJECTPROPERTY(id, 'IsProcedure') = 1 )
BEGIN
DROP PROCEDURE dbo.Parameter_1;
END;
GO
CREATE PROCEDURE Parameter_1 ( @parameterIn BIT )
AS
BEGIN
DECLARE @results TABLE ( col1 BIGINT, col2 INT )
INSERT INTO @results
( col1, col2 )
EXECUTE Parameter_2 @parameterIn;
INSERT INTO @results
( col1, col2 )
EXECUTE Parameter_3;
SELECT *
FROM @results;
END
GO
IF EXISTS ( SELECT 1
FROM sysobjects
WHERE id = OBJECT_ID('Parameter_2')
AND OBJECTPROPERTY(id, 'IsProcedure') = 1 )
BEGIN
DROP PROCEDURE dbo.Parameter_2;
END;
GO
CREATE PROCEDURE Parameter_2
(
@parameterIn VARCHAR(100)
)
AS
BEGIN
SELECT randomBigInt,
randomSmallInt
FROM #testEnvironment
WHERE randomBool = @parameterIn;
END
GO
IF EXISTS ( SELECT 1
FROM sysobjects
WHERE id = OBJECT_ID('Parameter_3')
AND OBJECTPROPERTY(id, 'IsProcedure') = 1 )
BEGIN
DROP PROCEDURE dbo.Parameter_3;
END;
GO
CREATE PROCEDURE Parameter_3
AS
BEGIN
SELECT 9223372036854775800,
2147483640
END
GO
Execute sproc -
EXECUTE Parameter_1 1;
CleanUp -
IF EXISTS ( SELECT 1
FROM sysobjects
WHERE id = OBJECT_ID('Parameter_1')
AND OBJECTPROPERTY(id, 'IsProcedure') = 1 )
BEGIN
DROP PROCEDURE dbo.Parameter_1;
END;
GO
IF EXISTS ( SELECT 1
FROM sysobjects
WHERE id = OBJECT_ID('Parameter_2')
AND OBJECTPROPERTY(id, 'IsProcedure') = 1 )
BEGIN
DROP PROCEDURE dbo.Parameter_2;
END;
GO
IF EXISTS ( SELECT 1
FROM sysobjects
WHERE id = OBJECT_ID('Parameter_3')
AND OBJECTPROPERTY(id, 'IsProcedure') = 1 )
BEGIN
DROP PROCEDURE dbo.Parameter_3;
END;
GO
IF OBJECT_ID('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;
GO
April 15, 2015 at 4:40 am
Whether you mis-understood or not:
EXECUTE Parameter_1 1;
That one line has shown me exactly what I was doing wrong.
Thank you very much for your assistance.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply