asked
Migration of Oracle Array to SQL Server
v_to_maillist := v_nominatorEmail; v_email_key_value_pair1 (1) (1) := 'Award Info : '; v_email_key_value_pair1 (1) (2) := v_awardinfo; v_email_key_value_pair1 (2) (1) := 'Nominee(s) : '; v_email_key_value_pair1 (2) (2) := v_nomineeinfo; v_email_key_value_pair1 (3) (1) := 'Nominated By : '; v_email_key_value_pair1 (3) (2) := v_nominatorinfo; v_email_key_value_pair1 (4) (1) := 'Modified By : '; v_email_key_value_pair1 (4) (2) := v_modifiedby; PKG_FORGE_CLIENT.USP_CREATE_NOTIFICATION(null, v_to_maillist, null, null, v_email_context, v_email_subject, v_email_content1, v_email_key_value_pair1, v_email_content2, p_iserror); **here i passing values as an array please someone tell me how to do this in sql server**
In SQL Server you could use a table-valued parameter :
http://msdn.microsoft.com/en-us/library/bb510489(v=sql.105).aspx Using the example in that BOL page as a base, here's an example tailored to your example /* Create a table type. */ CREATE TYPE KeyValuePair AS TABLE ( KeyName VARCHAR(50) , Value VARCHAR(50)); GO /* Create a procedure to receive data for the table-valued parameter. */ CREATE PROCEDURE YourProc @TVP KeyValuePair READONLY AS SET NOCOUNT ON select * FROM @TVP; GO /* Declare a variable that references the type. */ DECLARE @KeyValuePairTVP AS KeyValuePair; /* Add data to the table variable. */ INSERT INTO @KeyValuePairTVP (KeyName, Value) select 'Award Info','some award info' INSERT INTO @KeyValuePairTVP (KeyName, Value) select 'Nominees','some nominee info' INSERT INTO @KeyValuePairTVP (KeyName, Value) select 'Nominated By','some Nominated By info' INSERT INTO @KeyValuePairTVP (KeyName, Value) select 'Modified By','some Modified By info' /* Pass the table variable data to a stored procedure. */ EXEC YourProc @KeyValuePairTVP; GO
Yes as @SQLGoooRooo states, you can pass TVPs between stored procedures
yes you can reference another procedure's parameter but if you are unable to it could be either account access limits or simply ensure that you specify the fully qualified db name. Perhaps try EXEC DBName.YourProc @KeyValuePairTVP;
PKG_FORGE_CLIENT.USP_CREATE_NOTIFICATION(null, v_to_maillist, null, null, v_email_context, v_email_subject, v_email_content1, v_email_key_value_pair1, v_email_content2, p_iserror); in procedure USP_CREATE_NOTIFICATION how to take parameter having datatype table-valued parameter is it like @p_Data KeyValuePair readonly is it correct or not please tell me
15 People are following this question.
