Passing dynamic values to procedure parameters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Passing dynamic values to procedure parameters

Author

Topic

Nemesis85
Starting Member

6 Posts

Posted - 2011-01-28 : 05:40:46

Hi,
I am facing an issue which is how to pass values from a table to stored procedure parameters. and also how to concatenate values from this table together with simple text and pass it also to the procedure.

this is my script:

declare
@param1 varchar(15),
@param2 varchar(600),
@param3 int

set @param1 = (select phone from dbo.SMS_BMC )
set @param2 = 'the description is'+(select [description] from dbo.SMS_BMC) -- is it ok to make this concatenation ? or what is the correct script?
set @param3 = 1

exec Mafraq_SendSMS @param1,@param2,@param3

please your help..

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-28 : 05:51:07

Use like this

set @param2 = (select 'the description is'+isnull([description],'') from dbo.SMS_BMC)

Nemesis85
Starting Member

6 Posts

Posted - 2011-01-28 : 06:19:24

thanks MIK 2008.
How about if I need to concatenate values from more than one field and with many texts in between?

ex: [column1 name]+'text1]+[column2]+'text2'+...

is the same format works in this case?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-28 : 06:33:23

Not sure what are you trying to achieve however if you need Multiple fields data of Same Table to be concatenated with the string. Then it could be some how following:

(select 'the description is '+isnull([Col1],'')+' '+isnull([Col2],'')+' '+isnull([Col3],'') from dbo.SMS_BMC)

Col1, Col2 and Col3 are the fields of SMS_BMC

CheerS!

Nemesis85
Starting Member

6 Posts

Posted - 2011-01-28 : 06:45:49

I tried the format but it gives me an error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'the description is ' to data type int.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-28 : 06:50:08

seems that you are using a column having datatype as INT .... Any column that is of INT Datatype replace that column as below

ColumnName: Col1 (having datatype Int)
Replacement: ltrim(str(col1)) just replace the "Col1" in your statement with this one

Nemesis85
Starting Member

6 Posts

Posted - 2011-01-28 : 07:04:18

Thanks MIK_2008 it works!!
one more question. how can I schedule this script to be executed every second. As SQL job allows to schedule every 1 minute only

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-28 : 07:16:43

well come up with a different thread for this question. Also describe in detail what is your current data structure in terms of volume etc And most important WHY do you need it to be executed every second!

Going to leave the desk now :D

Nemesis85
Starting Member

6 Posts

Posted - 2011-01-28 : 07:47:16

I need it to update parameters in procedure which is responsible to send SMS messages. in other words I need real time executing to quickly sends SMS once the record inserted in the table.

Thanks for your kind assistance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources