Link to home
Create AccountLog in
Avatar of Stephen Forero
Stephen ForeroFlag for United States of America

asked on

set column equal to stored procedure in sql server mgmt studio

hi all,

I'm very new to sql server so please bear with me.
I made a very simple stored procedure.

basically a simple select statement of another table with one inpute parameter.

In a different table, how can I make the values of one of my columns equal to this stored procedure.

Thank you!!!
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Stored Procedures return recordsets, even if it's a single row / single column.

If your expectation is to do a SELECT @SomeVariable = dbo.SomeFunction(), then you need to build a scalar function that returns a single value of the same data type as @SomeVariable.
Avatar of Stephen Forero

ASKER

I'll give you my goal and if you could tell me your best approach it would be much appreciated....

I have a table with say 1000 rows and 10 columns.  I want 1 of these columns to be the results of the following

input 2 parameters,
  with parameter 1, use that as a lookup string to search a different table, and get the result.(always only 1 result in string format)

take parameter 2 and manipulate this string... basically like a if then statement.

then combine the result from lookup and the manipulation of second string.

and populate a particular column with the results... kinda like having a custom function as the contents of each cell in a column.

the input paraments come from the contents of whatever row I am hypothetically in.

hope this makes sense
(1)  Do a google search for 'T-SQL Scalar Function', and build one with your input two parameters.

(2)  The calling code would then go something like this.

Declare @foo whatever

SELECT @foo = dbo.YourFunction(@input_parameter_1, @input_parameter_2)

UPDATE YourTable
SET a_particular_column = @foo

>use that as a lookup string to search a different table
Be advised that if you really want the imput parameters to dictate which rows to update, then it's going to take awhile to get it right, 'cause you're playing with fire.
thank you, I'll start the search.

I think I didnt explain it fully.  I want every row to be updated with this custom formula.
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
this is perfect... I have now created my first scalar function!!  thanks!!!

one more thing though... of course... for the input parameter... how do I make it
@value_1 = "the value of the CURRENT row, in a particular column" ??

SET column_1 = dbo.column_function (@value_1, @value_2)
ahh, nevermind... it was a dumb question, just used
SET BloombergSymbol = dbo.scalar_LookupBBMsymbolv2(Product)

thanks!!
No, my fault, my first example usage [
UPDATE dbo.tablename1
SET column_1 = dbo.column_function (@value_1, @value_2)
]
was WRONG!


Should have been this:

UPDATE dbo.tablename1
SET column_1 = dbo.column_function (column2, column3)

as you've already figured out :-) .
and I actually changed it

added a column
named x AS dbo.column_function (column2, column3)

so now everytime the main data changes the calculated column is automatically updated

thanks for all the help!!!