Changing Stored Procedure 'type'
May 25, 2000Is there any way to change the 'type' of a stored procedure from 'user' to 'system'?
View 1 Replies
ADVERTISEMENT
Changing A Stored Procedure To Inline Sql
Jan 7, 2008Hi i have the following stored procedure, i am trying to convert it to inline sql, however i got stuck, because how would i output a value e.g "@Access_Right_ID", this is the stored procedure below
CREATE PROCEDURE dhoc_AccessRight_Insert @AccessLevel char(50), @Access_Right_ID int OUT,
@Tstamp timestamp out
ASSELECT * FROM tblAccess_Right
WHERE AccessLevel = @AccessLevel IF @@ROWCOUNT <>0 BEGIN RAISERROR('This record is already in the database',16,1) RETURN 14 END
ELSE BEGIN SET NOCOUNT OFF; INSERT INTO tblAccess_Right ( AccessLevel ) VALUES (@AccessLevel); SET @Access_Right_ID = @@Identity SET @Tstamp = (SELECT Tstamp FROM tblAccess_Right WHERE Access_Right_ID=@Access_Right_ID)
--Make sure this has saved, if not return 10 as this is unexpected error IF @@rowcount = 0 BEGIN RAISERROR('This record has not been inserted at this time, it might have been inserted by another user, please try again',16,1) RETURN 10 END ELSE BEGIN IF @@error <>0 BEGIN RETURN @@error END ENDEND
GO
Changing Databases In An Stored Procedure
Aug 24, 1999Hello all,
I have discovered that you cannot use the "USE" command in a stored procedure to change to another database.
Is there any way I can change to another database within a stored procedure besides calling another stored procedure on the database I want?
Thanks,
Nev.
Changing Database Within A Stored Procedure
Jul 20, 2005I need to create a stored procedure in the master database that canaccess info to dynamically create a view in another database. Itdoesn't seem like it should be very hard, but I can't get it to work.Here's an example of what I want to do.CREATE PROCEDURE create_view @dbname sysnameASBEGINDECLARE @query varchar(1000)SELECT @query = 'use ' + @dbname + ' go CREATE VIEW ........'EXEC(@query)ENDIn this case, I get an error with the word "go". Without it, I get a"CREATE VIEW must be the first statement in a batch" error. I tried asemicolon in place of "GO" but that didn't help either.Thanks
View 4 Replies View RelatedChanging An Activatin Stored Procedure
Mar 5, 2007Hello,
After I changed the activation stored procedure of a queue, the old activation stored procedure keeps being launched by the service associated with the queue. I turned the queue off and on, but to no avail. I also checked the dynamic view sys.service_queues to make sure the proper stored procedure exists in the field activation_procedure. Do you have any idea about what might have gone wrong?
Thanks
Changing Parameters To Wildcard In Stored Procedure
Mar 7, 2001I have a stored proc which will be fed a value that is compared to a char value for a selection. One of the choices might be 'all' for which I want to return all records. Something like this
create procedure name @location char(40)
as select ----------------
where locname like @location
I tried using LIKE COALESCE but no luck. Any ideas
Thanks
Changing Field Values In A Stored Procedure
May 4, 2003Here's the deal:
I import a flat file from a legacy system, and then convert it into a single table. That works simply enough.
Then I have a SP that querys that table using a parameter for an accountID. My business tier calls that SP and returns the results to the calling tier (my web application). Easy enough...
Now for the question. The people who created the flat file (written in COBOL) decided to use "codes" to represent data. So, for instance, if I'm looking for the account plan, I can expect to see characters like ], or [, or +, etc... These characters have a special meaning, like:
] = Plan A
[ = Plan B
+ = Plan C, and so on.
Currently, the web application displays those characters, but I want it to display the actual plan name. Is there a way that when I execute the SP, the SP could pull the necessary records, and whenever it encounters a certain "plan" character, it could convert it into a "readable" name? Say that it sees that the plan_type field has a value of "]" for twenty records, so it converts those twenty records' plan_type value from "]" into "Plan A"? I'm not sure if I can do that, but I want to at least evaluate the option if I can.
I've evaluated other options, like using a CASE statement in my code, but I shot that down quickly...for obvious reasons. I don't wanna be changing my web application or business tier each time these guys update a plan name, or add a new one, delete an existing one, etc...
I've also thought about creating a dictionary table than contains the plan's code and its name, and then just INNER JOIN the first table with the dict table. This would keep my SP very simple (it's very straight-forward right now, and I like that). That way, if a plan name is ever changed, or a new one is added, I simply update the dict table using a simple query. However, if my SP is doing the conversion, I could just as easily update the SP.
Either of these methods would work for me, and I *do* know how to do the latter (dict table). However, there are quite a few other fields that I may have to do this for. I believe when I left for the day on Friday, my last count was 14 fields total that needed translation. That would mean 14 different dict tables! That could certainly affect my SP performance with all those INNER JOINS!
Therefore, I'm certainly interested in figuring out if it's possible to do the former method (SP), and then I shall decide which method is best for my situation.
Feel free to include your thoughts on which process you think is better as well. I'm really riding the fence with this one. However, if I can't find out how to change field values in my SP, then obviously I'll make a decision very quickly...
Thanks in advance.
Stored Procedure For Changing Recovery Model ?
Mar 29, 2004The SQL Server is running full recovery model but they would like it to be changed to simple while the data backup occurs then set back to full. Is there a way to do this in a stored procedure or is it all done via options ? Thanks
View 8 Replies View RelatedChanging Stored Procedure Freezes Crystal
Aug 18, 2006After I change a fairly complex stored procedure and I run a reportagainst it, crystal hangs at "assesing database". I have verified thedatabase. When I run a trace on SQL is shows repeated cachemiss overand over. I let it run for 30 minutes and nothing.Anyone?
View 5 Replies View RelatedChanging Field In A Stored Procedure To Match Name In A Form
May 15, 2008If I have a column named "Login" in a SQL Table (I am sharing with another application) that I am using a stored procedure to acquire the information from, how can I trranspose its name to match code already written in a Web App to get the data.
There is a web app already created that has the followig code to get the data from the database
Dim strSQL ast string = "UsersSelectCommand"
intLoginID = objDataReader("LoginID")
My stored procedure is the following:
CREATE PROCEDURE UsersSelectCommand/* ( @parameter1 datatype = default value, @parameter2 datatype OUTPUT )*/AS Select Lastname, FirstName, Login from Users Order by LastName
GO
The stored procedure will return "Login" instead of "LoginID" that I am wanting. How can I modify the Stored Procedure to change the LoginID to Login.
Best Way To Send Email From A Stored Procedure (dynamically Changing Paramenters And Attachment)
Jul 23, 2005Hello everyone,I need advice of how to accomplish the following:Loop though records in a table and send an email per record. Emailrecipient, message text and attachment file name - that's all changesrecord by record.Is it doable from a stored procedure (easily I mean, or am I better offwriting a VB app)? There are so many options of sending mail from SQLserver - CDONTS, SQL MAIL TASK, xp_sendmail. What's easier to implementand set up?Thanks a lot!!!(links and fragments of sample code would be greatlyappreciated)Larisa
View 2 Replies View RelatedBit Type Parameter For Stored Procedure
Apr 19, 2004I am trying to supply a bit type parameter to a stored procedure. This is used to update a Bit type field in a table. The field is called PDI
The syntax I am trying to use is:
MyStoredProcedure.Parameters.Add(New SqlParameter("@Pdi",SqlDbtype.bit))
MyStoredProcedure.Parameters("@pdi").value = -1
When I do my ExecuteNonQuery I get error 8114
What am I doing wrong?
Getting Return Type From Stored Procedure
Sep 7, 2006Hi all,
I am trying to figure out a way to analyze a stored procedure to deduce its output type.
A stored procedure can return values throught one of the following:
1) Using the Return keyword
2) Returning a recordset
3) Throught its Output parameters
4) A combination of 1, 2 or 3
Using the Information_Schema, you can access the SQL code of the stored procedure from, say, VB.Net. From there, I want to know what output type the stored procedure has.
A) for 1), I guess I can parse the code for the 'Return' keyword, but that wouldn't be efficient, for the 'Return' word could be inserted into string values...
B) for 2), it would be difficult to analyze the stored procedure's code to know FOR SURE whether a recordset is returned. You just can't parse for SELECT keywords in complex code...
C) 3) should be easy enough, getting the return parameters types from the Information_Schema.
So, is there an easy way that I missed for achieving this, some object in the .Net Framework that I could use to analyze SQL queries perhaps, or am I doomed to do it the hard way?
Thanks,
-Etienne
Stored Procedure Parameter Without Type
May 23, 2006Hi All !
How i can create a stored procedure like ISNULL ?
I need to a procedure that accepts Int type parameters and Float type ect.
Thanks. DBT.
Is It Possible To Capture An OUT Type Parameter From A PL/SQL Stored Procedure?
Dec 19, 2007When a stored PL/SQL procedure in my Oracle database is called from ASP.NET, is it possible to retrieve the OUT parameter from the PL/SQL procedure? For example, if I have a simple procedure as below to insert a row into the database. Ideally I would like it to return back the parameter named NewId to my ASP.NET server. I'd like to capture this in the VB.NET code. 1 create or replace procedure WriteName(FirstName in varchar2, LastName in varchar2, NewId out pls_integer) is2 3 NameId pls_integer;4 5 begin6 7 select name_seq.nextval into NameId from dual;8 9 insert into all_names(id, first_name, last_name)10 values(NameId, FirstName, LastName);11 12 NewId := NameId;13 14 end WriteName; 1 <asp:SqlDataSource 2 ID="SqlDataSaveName" 3 runat="server" 4 ConnectionString="<%$ ConnectionStrings:ConnectionString %>"5 ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 6 SelectCommand="WRITENAME"7 SelectCommandType="StoredProcedure">8 <SelectParameters>9 <asp:ControlParameter ControlID="TextBoxFirstName" Name="FIRSTNAME" PropertyName="Text" Type="String" />10 <asp:ControlParameter ControlID="TextBoxLastName" Name="LASTNAME" PropertyName="text" Type="String" />11 </SelectParameters>12 </asp:SqlDataSource>This is then called in the VB.NET code as below. It is in this section that I would like to capture the PL/SQL OUT parameter NewId returned from Oracle. 1 SqlDataSaveName.Select(DataSourceSelectArguments.Empty) If anybody can help me with the code I need to add to the VB.NET section to capture and then use the returned OUT parameter then I'd be very grateful.
View 2 Replies View RelatedData Type Conversion In Stored Procedure
Aug 2, 2006Hi,
I have a stored procedure a portion of which looks like this:
IF (CAST (@intMyDate AS datetime)) IN (
SELECT DISTINCT SHOW_END_DATE
FROM PayPerView PP
WHERE PP_Pay_Indicator = 'N' )
BEGIN
--ToDo Here
END
where:
@intMyDate is of type int and is of the form 19991013
SHOW_END_DATE is of type datetime and is of the form 13/10/1999
however when I run the procedure in sql query analyzer as:
EXEC sp_mystoredproc param1, param2
i get the error:
Server: Msg 242, Level 16, State 3, Procedure usp_Summary_Incap, Line 106
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
what is the proper way of doing the conversion from int to datetime in stored procedure?
thank you!
cheers,
g11DB
EXPERT: Stored Procedure Operand Type Clash
Aug 18, 2006Im trying to insert a new record into tblMessages...the unique ID generated for MessageID is the value I want to use as a value for the MessageID field when inserting a new record in tblUsersAndMessagesI have the following sp:
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE spNewMessage
@UserIDSender nvarchar(256),
@MessageTitle nvarchar(50),
@MessageContent text,
@MessageType int,
@MessageID uniqueidentifier,
@UserID uniqueidentifier
AS
Begin
Set NoCount on
DECLARE @WhateverID uniqueidentifier
INSERT INTO tblMessages(UserIDSender,MessageTitle,MessageContent,MessageType)
VALUES (@UserIDSender,@MessageTitle,@MessageContent,@MessageType)
Select @WhateverID=@@Identity
INSERT INTO tblUsersAndMessages(MessageID,UserID)
VALUES (@WhateverID,@UserID)
End
GO
And here are my tables:tblUsersAndMessages allow nullsMessageID uniqueidentifier falseUserID uniqueidentifier falseNew bit false *default set to ((1))
tblMessages allow nullsMessageID uniqueidentifier false *PK default set to (newid())UserIDSender uniqueidentifier falseMessageTitle nvarchar(50) trueMessageContent text trueSentDateTime datetime false * default set to (getdate())MessageType int false
Now when I try to add my SP I get this error:Msg 206, Level 16, State 2, Procedure spNewMessage, Line 22Operand type clash: numeric is incompatible with uniqueidentifierWhat can this be?!?
Data Type Of Parameter Passing To A Stored Procedure
Feb 25, 2004Hi,
I pass a paramter of text data type in sql server (which crosspnds Memo data type n Access) to a stored procedure but the problem is that I do not know the crossponding DataTypeEnum to Text data type in SQL Server.
The exact error message that occurs is:
ADODB.Parameters (0x800A0E7C)
Parameter object is improperly defined. Inconsistent or incomplete information was provided.
The error occurs in the following code line:
.parameters.Append cmd.CreateParameter ("@EMedical", advarwchar, adParamInput)
I need to know what to write instead of advarwchar?
Thanks in advance
How Do I Change The Type Of A Stored Procedure From User To System?
Aug 11, 2005I built a database by using a generated script from the originaldatabase. It built the System Store Procedures as User type. How do Ichange them back to System type?
View 4 Replies View RelatedVariable Type Errors When Calling Stored Procedure
May 12, 2008I currently have a stored procedure that is defined as follows:
CREATE PROCEDURE UpdateSyncLog
@TableName char(100),
@LastSyncDateTime datetime,
@ErrorState int OUTPUT
I am using an execute sql task to call this procedure. The connectiontype is ADO .NET and the SQLSourceType is DirectInput. The IsQueryStoredProcedure setting is false, and the following is my SQL Statement I have entered:
exec UpdateSyncLog 'myTestTable', @LastSyncDateTime, @ErrorState
Result set is set to None, as this query returns NO results (i.e. has no select statements in it that returns results).
I have two variables in this SSIS package. CurrentDateTime, and ErrorStateVal. CurrentDateTime is of Data type DateTime, the ErrorStateVal is of type Int32
The parameter mappings are as follows:
Varialbe Name=User::CurrentDateTime, Direction=Input, DateType=DateTime, Parameter Name=@LastSynDateTime, Parameter Size=-1
Variable Name=User::ErrorStateVal, Direction=Output, DateType=Int32, Parameter Name=@ErrorState, Parameter Size=-1
The error I am getting when running this execute sql task is as follows:
Error: 0xC001F009 at AS400 to SQL Full Repopulation Sync: The type of the value being assigned to variable "User::ErrorStateVal" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec UpdateSyncLog 'myTestTable', @LastSyncDateTime, @ErrorState" failed with the following error: "The type of the value being assigned to variable "User::ErrorStateVal" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task
This makes no sense to me, both the SSIS variable ErrorStateVal is Int32, as well as the parameter declaration in the Execute SQL task is Int32 with direction of OUTPUT, and my stored procedure definition has @ErrorState as an integer as well.
What gives?
Converting Date Data Type In Stored Procedure
Jul 31, 2006HI Experts...
I am using SQL SERVER 2005 standard edition
I have encountered a problem regarding converting date data type in stored procedure
As i was having problem taking date as input parameter in my stored procedure, so, then I changed to varchar (16) i.e.
CREATE PROCEDURE sp_CalendarCreate
@StDate VARCHAR(16) ,
@EDate VARCHAR(16),
then I am converting varchar to date with following code
DECLARE @STARTDATE DATETIME
DECLARE @ENDDATE DATETIME
SELECT @STARTDATE = CAST(@STDATE AS DATETIME)
SELECT @ENDDATE = CAST(@EDATE AS DATETIME)
When I try to execute the procedure with following code
execute sp_CalendarCreate @stdate='12-1-06',@edate='20-1-06'
but it gives me following error
Msg 242, Level 16, State 3, Procedure sp_CalendarCreate, Line 45
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Can any one tell me the solution of that problem (at ur earliest)
regards,
Anas
Convert Data File Type From Unix To Pc Using Stored Procedure?
Jul 14, 2006Hi, I have a script written in ASP to load data file (.csv) to ms sql. In the script, I have a portion of script looks like taht :
.....
Do While NOT oInFile.AtEndOfStream oOutFile.WriteLine Replace(oInFile.Readline, chr(13), vbcrlf)Loop
.....
After that, I will use a BULK INSERT to input data to ms sql.
I am wondering how do I convert each row (data) to vbcrlf in Stored Procedure? Coz' I did not compose the convertion part, and I got no error when running BULK INSERT, but no rows are inserted :( HELP!!!!
I guess it's because the file is not being converted into a correct format??
How To Deal With Image Data Type Within Stored Procedure When Using SQL2000?
Dec 5, 2007Hi,
Does anyone know how to deal with image data type within stored procedure when using SQL2000?
I have a table and there is an image data type column. In this table I need to make a copy of one row within a table through a SP /it means to retrieve the whole row within SP, change another columns data (but, that the image is not modified) / and save the modified row back to the same table.
Problem is, that within SP is not alowed to use local varaibles of image data type. Does anyone know a solution for this? Please.
Thank you.
Stored Procedure:calculating Minus Dependent On Type With Group By
Aug 6, 2004Hi i am trying to get the following result:
CALCULATE THE CURRENT NUMBER OF SHARES on base of number of shares bought and number of shares sold
so the logic is
int total
if transactiontype is "buy", total = total + amount bought
if transactiontype is sell, total = total-amount sold
plus must be grouped by share
base data are stored in 1 table as
share name transaction type share amount
tesco buy 200
tesco sell 50
sounds rather easy does it not?
well sql syntax must be very foreign to me as i do not manage it
I tried a few options:
1 to write a database function as above and then call it from sproc
but i need to use group by share name or id so it calculates for each share and it will not let me to use function in select with group by
2 i tried to use if within sproc but i get complain incorrect syntax (I use MSDE database accessed via Visual) - I think it suppose to be possible but it throws me out with the most simple if or = is there some special syntax?
3 I created 2 views: 1 view: all sold shares, 2nd view: all bought shares and their bought/sold quantities
as in
select share name, sum(sharequantity)
where transaction = sell
from table
group by share name
and the same for purchases
then I did union of these 2 views with boughtquantity - soldquantity what worked but it only showed me shares which were sold, if share was never sold it was not shown I guess due to union
so I am still stuck!
User Defined Data Type Used In Stored Procedure Parameters
Jul 23, 2005I have several stored procedures with parameters that are defined withuser defined data types. The time it takes to run the procedures cantake 10 - 50 seconds depending on the procedure.If I change the parameter data types to the actual data type such asvarchar(10), etc., the stored procedure takes less that a second toreturn records. The user defined types are mostly varchar, but someothers such as int. They are all input type parameters.Any ideas on why the stored procedure would run much faster if notusing user defined types?Using SQL Server 2000.Thanks,DW
View 13 Replies View RelatedOutput Parameter With Text Data Type In Stored Procedure
Jul 20, 2005How can I make a stored procedure which has a output parameter withtext data type? My procedure is:CREATE PROCEDURE try@outPrm text OutputASselect @outPrm =(select field1 from databaseName Wherefield2='12345')GOHere field1 is text data type.Thanks
View 1 Replies View Related.NET Framework :: CLR Stored Procedure With Table Data Type Parameter
May 27, 2015I want to create a CLR Stored Procedure with a Table User Defined Type like this short example in SQL:
CREATE TYPE [dbo].[TypeTable] AS TABLE
(
[Id] [integer] NOT NULL,
[Value] [sysname] NOT NULL,
PRIMARY KEY CLUSTERED
[Code] ....
I found some example in this link : CLR UDT
But I can't figure how to do the same with a User Defined Type Table.
How To Conditionally Or Programmatically Run A Stored Procedure With Command Type Set To Storedproc?
May 21, 2008I'm using SQL RS 2005 and have a report where we want the report to run a different stored procedure depending on if a condition is true. I've set my 'command type' to stored proc and can type in the name of a stored procedure. If I type in just one stored procedure's name, it runs fine. But if I try to use a =IIF(check condition, if true run stored proc 1, if false run storedproc 2) then the exclamation (run) button is greyed out. Does anyone know how I can do this? Thanks.
View 3 Replies View RelatedHow Can I Change The Data Type Of The Parameter For The Deployed Stored Procedure ??
Jan 11, 2006Hi
I have Try to Create Stored Procedure in C# with the following structure
[Microsoft.SqlServer.Server.SqlProcedure]
public static void sp_AddImage(Guid ImageID, string ImageFileName, byte[] Image)
{
}
But when I try to deploy that SP to SQL Server Express , The SP Parameters become in the following Stature
@ImageID uniqueidentifier
@ImageFileName nvarchar(4000)
@Image varbinary(8000)
But I don€™t want that Data types .. I want it to be in the following format
@ImageID uniqueidentifier
@ImageFileName nText
@Image Image
How Can I Control the data type for each parameter ??
Or
How Can I Change the data type of the parameter for the Deployed Stored Procedure ??
Or
How Can I defined the new Data type ??
Or
What's the solution to this problem ??
Note : I get Error when I try to use Alert Statement to change the parameter Data type for the SP
ALTER PROCEDURE [dbo].[sp_AddImage]
@ImageID [uniqueidentifier],
@ImageFileName nText,
@Image Image
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [DatabaseAndImages].[StoredProcedures].[sp_AddImage]
GO
And thanks with my best regarding
Fraas
Syntax To Pass A Parameter Of Type Nvarchar(max) To Stored Procedure
Dec 26, 2007I have a stored procedure that contains a paramteter of type nvarchar(max). What is the syntax to pass this parameter to the sp from a VB.Net application. Specifically, I need to know what to put in the 3rd parameter below:
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, , Name)
Error Converting Data Type Varchar To Numeric In Stored Procedure
Aug 28, 2012Here's the SP
Code:
USE [byrndb]
GO
/****** Object: StoredProcedure [dbo].[sp_GetLikeJobsByJobNumber] Script Date: 08/28/2012 15:17:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
Why when I run
Code:
USE [byrndb]
GO
DECLARE@return_value int
EXEC@return_value = [dbo].[sp_GetLikeJobsByJobNumber]
@number = N'23913'
SELECT'Return Value' = @return_value
GO
Am I getting an "Msg 8114, Level 16, State 5, Procedure sp_GetLikeJobsByJobNumber, Line 16
Error converting data type varchar to numeric." error? Line 16 is "@number varchar = null"
Input Parameter Exceeds The Limit Of The Data Type's Length In Stored Procedure
Sep 4, 2007
Hi guys, is there any way to solve my problem as title ? Assuming my stored proc is written as below :
CREATE PROC TEST
@A VARCHAR(8000) = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,...,5000'
AS
BEGIN
DECLARE @B nvarchar(MAX);
SET @B = 'SELECT * FROM C WHERE ID IN ( ' + @A + ')'
EXECUTE sp_executesql @B
END
GO
Create A Compatible Function That Can Invoke A Stored Procedure Without Writing The ParameterName And Remembing The Type And Size....
May 30, 2008suppose,the type of the stored procedure's paramters is varchar .I hate to add parameterNames and types.If i can read the string of the stored procedure the get the paramterNames by operating text?
public void storeOperate(string stringParameter,string name) { string[] strs=stringParameter.Split('&'); SqlConnection conn = new SqlConnection(getConnectionString.getconnectionString()); SqlCommand cmd=new SqlCommand(name,conn); cmd.CommandText=name; cmd.CommandType=CommandType.StoredProcedure; foreach(string str in strs) { cmd.Parameters.Add(".....",SqlDbType........).Value=str; //my trouble } conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); cmd.Dispose(); }