June 14, 2009 at 11:36 pm
Hi, all. I've been searching for the answer on this for too long, and it's probably just a matter of some simple syntax adjustments - I just can't afford the time to keep not finding the answer...
I've been doing ASP for years and could accomplish this in about 2 seconds using that, so I'm somewhat knowledgeable in the concepts of passing data around for various database activities.
This SP gets some of its data from our CRM and the way it passes that data is non-negotiable, so I'm here jumping with both feet into the deep end of the SP pool.
Here's what works as expected:
----------
CREATE PROCEDURE SP_name (it doesn't really start with SP_)
@variable_1_from_CRM varchar(13),
@variable_2_from_CRM varchar(10),
@variable_3_from_CRM char(13),
@variable_4_from_CRM char(10),
@flag varchar(1) output (also for CRM so that it knows if the SP was successful)
AS
INSERT INTO table_name
(fld1, fld2, fld3, fld4)
VALUES
(@variable_1_from_CRM, @variable_2_from_CRM, @variable_3_from_CRM, @variable_4_from_CRM)
----------
HOWEVER:
When I add my own variable and try to populate it like so, it fails:
Same code as above, but with this:
@Activity_Title varchar(100),
between the last "@flag" and second to last "@variable_4_from_CRM", then after the "AS" line but before the "INSERT" line I added this:
SELECT @Activity_Title = (SELECT title FROM other_table WHERE unique_ID = @variable_4_from_CRM)
So what I'm trying to do is extremely simple to me from an ASP standpoint, but I'm just not getting something right here in the SP. All I need to do is take one of the variables passed to the SP from my CRM and put it into a select statement that should only return one field from one record, then insert the text from that field into another table - same varchar(100) on both source & target fields as well as the variable.
Thx!
June 15, 2009 at 12:33 am
Hi,
Are you looking for something like this?
the select query should return only one record.
CREATE PROCEDURE SP_name (it doesn't really start with SP_)
@variable_1_from_CRM varchar(13),
@variable_2_from_CRM varchar(10),
@variable_3_from_CRM char(13),
@variable_4_from_CRM char(10),
@flag varchar(1) output (also for CRM so that it knows if the SP was successful)
AS
declare @Activity_Title varchar(100)
set @Activity_Title = (SELECT top 1 title FROM other_table WHERE unique_ID = @variable_4_from_CRM)
INSERT INTO table_name
(fld1, fld2, fld3, fld4, fld5)
VALUES
(@variable_1_from_CRM, @variable_2_from_CRM, @variable_3_from_CRM, @variable_4_from_CRM,@Activity_Title)
thanks
June 15, 2009 at 12:48 am
That did it! Thanks! I was hoping it would be as simple as just moving some things around & getting the correct syntax in place. I was treating the SP variables like ASP variables - didn't know that my "custom" variable had to be "DECLARE"ed instead of just listing it at the top with the others that grab the data from the CRM. Also not sure why the "TOP 1" was necessary in the SELECT statement since there will be only one record returned...
June 15, 2009 at 3:41 am
then top 1 is not required. i put it just to make sure it returns only 1 record
thanks
June 15, 2009 at 6:04 am
jpalmer (6/15/2009)
I was treating the SP variables like ASP variables - didn't know that my "custom" variable had to be "DECLARE"ed instead of just listing it at the top with the others that grab the data from the CRM.
Those ones at the top, between the CREATE PROCEDURE and the AS aren't variables. They're parameters to the procedure, the same as parameters in a VB function or procedure. By placing a name and type there you're saying that the caller will pass that value, or will pass a variable for an output parameter.
Local variables (DIM x AS Integer in VB) are declared using the DECLARE statement within the body of the procedure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 16, 2009 at 7:45 am
You can replace
set @Activity_Title = (SELECT title FROM other_table WHERE unique_ID = @variable_4_from_CRM)
with
select @Activity_Title = title FROM other_table WHERE unique_ID = @variable_4_from_CRM
Note that if the SELECT statement did return more than one row, @Activity_Title would take the value from the 'last' row.
If you wish to use TOP 1 and receive the value from the 'first' row, the statement becomes
select TOP 1 @Activity_Title = title FROM other_table WHERE unique_ID = @variable_4_from_CRM
Note that 'first' and 'last' are both arbitrary unless you also have an ORDER BY clause in the SELECT statement.
Martin Wills
June 17, 2009 at 9:26 am
You could also...
CREATE PROCEDURE SP_name (it doesn't really start with SP_)
@variable_1_from_CRM varchar(13),
@variable_2_from_CRM varchar(10),
@variable_3_from_CRM char(13),
@variable_4_from_CRM char(10),
@flag varchar(1) output (also for CRM so that it knows if the SP was successful)
AS
declare @Activity_Title varchar(100)
INSERT INTO table_name
(fld1, fld2, fld3, fld4, fld5)
SELECT top 1 @variable_1_from_CRM, @variable_2_from_CRM, @variable_3_from_CRM, @variable_4_from_CRM, title
FROM other_table WHERE unique_ID = @variable_4_from_CRM
Randy
June 17, 2009 at 9:38 am
Quite right, Randy (and you don't need the DECLARE statement).
The OP asked about populating a SP variable but, as you say, he could satisfy the requirements without doing so.
But the other answer(s) show how a SP variable can be populated.
So twice the value.
Martin Wills
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply