July 21, 2009 at 9:39 am
I am trying to pass some in/out stored procedure parameters and cant get it working. Any suggestions ?
Thanks
declare @skill1 nvarchar(20)
declare @skill2 nvarchar(20)
declare @skill3 nvarchar(20)
declare @skill4 nvarchar(20)
declare @email nvarchar(1000)
select @skill1 = 'livelink'
select @skill2 = 'sharepoint'
select @skill3 = 'java'
select @skill4 = 'j2ee'
exec usp_getEmails @skill1, @skill1, @skill3, @skill4
select @skill1, @skill2, @skill3, @skill4 output @email
Stored Procedure
ALTER PROCEDURE usp_getEmails @skill1 nvarchar(20), @skill2 nvarchar(20), @skill3 nvarchar(20), @skill4 nvarchar(20), @email nvarchar(1000)
as
Begin
CREATE TABLE #OutputEmails
(
Email1 nvarchar(1000),
Email2 nvarchar(1000),
Email3 nvarchar(1000),
Email4 nvarchar(1000),
Email5 nvarchar(1000),
Email6 nvarchar(1000),
Email7 nvarchar(1000),
Email8 nvarchar(1000),
Email9 nvarchar(1000),
Email10 nvarchar(1000)
);
INSERT INTO #OutputEmails SELECT Email1,Email2,Email3,Email4,Email5,Email6,Email7,Email8,Email9,Email10 FROM OutputResume3
where (contains (originalresume, '"@skill1"
and "@skill2"
and "@skill3"
and "@skill4"'))
--where (contains (originalresume, '"ts/sci"
--and "livelink"
--and "sharepoint"'))
SELECT DISTINCT @Email FROM
(
SELECT Email1 as Email FROM #OutputEmails
UNION
SELECT Email2 as Email FROM #OutputEmails
UNION
SELECT Email3 as Email From #OutputEmails
Union
SELECT Email4 as Email From #OutputEmails
UNION
SELECT Email5 as Email FROM #OutputEmails
UNION
SELECT Email6 as Email From #OutputEmails
Union
SELECT Email7 as Email From #OutputEmails
UNION
SELECT Email8 as Email FROM #OutputEmails
UNION
SELECT Email9 as Email From #OutputEmails
Union
SELECT Email10 as Email From #OutputEmails
) as T where Email like '%@%'
End
July 21, 2009 at 10:09 am
The parameter needs to be declared as OUTPUT in the procedure definition.
@skill4 nvarchar(20) OUTPUT
July 21, 2009 at 10:17 am
Do you mean like this ?
declare @skill1 nvarchar(20)
declare @skill2 nvarchar(20)
declare @skill3 nvarchar(20)
declare @skill4 nvarchar(20)
declare @email nvarchar(1000)
select @skill1 = 'livelink'
select @skill2 = 'sharepoint'
select @skill3 = 'java'
select @skill4 = 'j2ee'
exec usp_getEmails @skill1, @skill1, @skill3, @skill4, @email
--select @skill1, @skill2, @skill3, @skill4
OUTPUT
select @email
Stored Procedure
ALTER PROCEDURE usp_getEmails @skill1 nvarchar(20), @skill2 nvarchar(20), @skill3 nvarchar(20), @skill4 nvarchar(20), @email nvarchar(1000) OUTPUT
as
Begin
CREATE TABLE #OutputEmails
(
Email1 nvarchar(1000),
Email2 nvarchar(1000),
Email3 nvarchar(1000),
Email4 nvarchar(1000),
Email5 nvarchar(1000),
Email6 nvarchar(1000),
Email7 nvarchar(1000),
Email8 nvarchar(1000),
Email9 nvarchar(1000),
Email10 nvarchar(1000)
);
INSERT INTO #OutputEmails SELECT Email1,Email2,Email3,Email4,Email5,Email6,Email7,Email8,Email9,Email10 FROM OutputResume3
where (contains (originalresume, '"@skill1"
and "@skill2"
and "@skill3"
and "@skill4"'))
--where (contains (originalresume, '"ts/sci"
--and "livelink"
--and "sharepoint"'))
SELECT DISTINCT @Email FROM
(
SELECT Email1 as '@Email' FROM #OutputEmails
UNION
SELECT Email2 as '@Email' FROM #OutputEmails
UNION
SELECT Email3 as '@Email' From #OutputEmails
Union
SELECT Email4 as '@Email' From #OutputEmails
UNION
SELECT Email5 as '@Email' FROM #OutputEmails
UNION
SELECT Email6 as '@Email' From #OutputEmails
Union
SELECT Email7 as '@Email' From #OutputEmails
UNION
SELECT Email8 as '@Email' FROM #OutputEmails
UNION
SELECT Email9 as '@Email' From #OutputEmails
Union
SELECT Email10 as '@Email' From #OutputEmails
) as t where '@Email' like '%@%'
End
I have no errors, but no results. I think it is the 't' field at the end, but i'm not sure how to pass it back to the execute statement ?
Thanks
July 21, 2009 at 10:43 am
ALTER PROCEDURE usp_getEmails @skill1 nvarchar(20), @skill2 nvarchar(20), @skill3 nvarchar(20), @skill4 nvarchar(20) OUTPUT, @email nvarchar(1000)
July 21, 2009 at 11:04 am
That worked, thanks!
In the stored procedure, when i have a single parameter passed to the CONTAINS statement it works. When i have multiple parameters passed, i dont get any results.
Do you know what format the syntax needs to be to make this work ?
I have tried single quotes, double quotes, parenthesis without luck.
Thanks
ALTER PROCEDURE usp_getEmails @skill1 nvarchar(20), @skill2 nvarchar(20), @skill3 nvarchar(20), @skill4 nvarchar(20) OUTPUT, @email nvarchar(1000)
as
Begin
DECLARE @allskills nvarchar(100)
SET @allskills='@skill1 and @skill2 and @skill3 and @skill4'
CREATE TABLE #OutputEmails
(
Email1 nvarchar(1000),
Email2 nvarchar(1000),
Email3 nvarchar(1000),
Email4 nvarchar(1000),
Email5 nvarchar(1000),
Email6 nvarchar(1000),
Email7 nvarchar(1000),
Email8 nvarchar(1000),
Email9 nvarchar(1000),
Email10 nvarchar(1000)
);
INSERT INTO #OutputEmails SELECT Email1,Email2,Email3,Email4,Email5,Email6,Email7,Email8,Email9,Email10 FROM OutputResume3
--where (contains (originalresume, @skill1))
where (contains (originalresume, @allskills))
--and @skill3 and @skill4))
--where (contains (originalresume, '"ts/sci"
--and "livelink"
--and "sharepoint"'))
SELECT DISTINCT Email FROM
(
SELECT Email1 as Email FROM #OutputEmails
UNION
SELECT Email2 as '@Email' FROM #OutputEmails
UNION
SELECT Email3 as '@Email' From #OutputEmails
Union
SELECT Email4 as '@Email' From #OutputEmails
UNION
SELECT Email5 as '@Email' FROM #OutputEmails
UNION
SELECT Email6 as '@Email' From #OutputEmails
Union
SELECT Email7 as '@Email' From #OutputEmails
UNION
SELECT Email8 as '@Email' FROM #OutputEmails
UNION
SELECT Email9 as '@Email' From #OutputEmails
Union
SELECT Email10 as '@Email' From #OutputEmails
) as t where '@Email' like '%@%'
End
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply