Data Type Of Parameter Passing To A Stored Procedure

Feb 25, 2004

Hi,
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

View 1 Replies


ADVERTISEMENT

Output Parameter With Text Data Type In Stored Procedure

Jul 20, 2005

How 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, 2015

I 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.

View 5 Replies View Related

How Can I Change The Data Type Of The Parameter For The Deployed Stored Procedure ??

Jan 11, 2006

Hi
 
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

View 7 Replies View Related

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

View 2 Replies View Related

Parameter Passing To A Stored Procedure

Mar 31, 2004

Hey huys, I got this stored procedure. First of all: could this work?

--start :)
CREATE PROCEDURE USP_MactchUser

@domainUserID NVARCHAR(50) ,

@EmployeeID NVARCHAR(50) ,

@loginType bit = '0'
AS

INSERT INTO T_Login

(employeeID, loginType, domainUserID)

Values
(@EmployeeID, @loginType, @domainUserID)
GO
--end :)

then I got this VB.Net code in my ASP.net page....

---begin :)
Private Sub matchUser()
Dim insertMatchedUser As SqlClient.SqlCommand
Dim daMatchedUser As SqlClient.SqlDataAdapter
SqlConnection1.Open()
'conn openned
daMatchedUser = New SqlClient.SqlDataAdapter("USP_MatchUser", SqlConnection1)
daMatchedUser.SelectCommand.CommandType = CommandType.StoredProcedure
daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@EmployeeID", SqlDbType.NVarChar, 50))
daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@domainUserID", SqlDbType.NVarChar, 50))
daMatchedUser.SelectCommand.Parameters("@EmployeeID").Value = Trim(lblEmployeeID.Text)
daMatchedUser.SelectCommand.Parameters("@domainUserID").Value = Trim(lblDomainuserID.Text)
daMatchedUser.SelectCommand.Parameters("@EmployeeID").Direction = ParameterDirection.Output
daMatchedUser.SelectCommand.Parameters("@domainUserID").Direction = ParameterDirection.Output
SqlConnection1.Close()
'conn closed

End Sub
---

If I try this it doesn't work (maybe that's normal :) ) Am I doing it wrong. The thing is, in both label.text properties a values is stored that i want to as a parameter to my stored procedure. What am I doing wrong?

View 2 Replies View Related

Passing A Parameter Into A Stored Procedure In A Report ..........

May 8, 2007

Hi,  I have found this question asked many times online, but the answers always consisted of getting a parameter from the user and to the report.  That is NOT what everyone was asking.  I would like to know how to pass a parameter I already have into my stored procedure. Using Visual Studio 2005, in the "Data" tab of a report calling something likeUnique_Login_IPsreturns correctly when that stored proc takes no params, but when I have one that takes the year, for example, I do not know how to pass this info in.  Note: the following does not work in this context : EXEC Unique_Login_IPs ParameterValue   nor does  EXEC Some_proc_without_paramsVisual studio seems to want only the procedure name because it returns errors that say a procedure with the name "the entire line above like EXEC Unique_Login_IPs ParameterValue" does not exist.... Thanks in advance,Dustin L 

View 1 Replies View Related

Passing Dropdownlistitem As A Parameter To Stored Procedure

Apr 25, 2008

I am having a dataentry screen. Some columns i will fill the columns by typing and for some columns i will choose an item from the dropdownlist and i have to send it as an input parameter to stored procedure. How to do that? Pls explain
Thanx

View 3 Replies View Related

Error On Passing Parameter To Stored Procedure

Jul 1, 2004

Hi,

I have a procedure that will save to table in sql server 200 via stored procedure. When I hit the save button it alwasy give me an error saying "Procedure 'sp_AddBoard' expects parameter '@dtmWarrantyStart', which was not supplied" even though I supplied it in the code

which is

Dim ParamdtmWarrantyStart As SqlParameter = New SqlParameter("@dtmWarrantyStart", SqlDbType.datetime, 8)
ParamdtmWarrantyStart.Value = dtmWarrantyStart
myCommand.Parameters.Add(ParamdtmWarrantyStart)

below is the stored procedure.

create Proc sp_AddBoard(@BrandID int,
@strPcName varchar(50),
@bitAccounted bit,
@dtmAccounted datetime,
@dtmWarrantyStart datetime,
@dtmWarrantyEnd datetime,
-- @strDescription varchar(500),
@intStatus int,
@ModelNo varchar(50),
@intMemorySlots int,
@intMemSlotTaken int,
@intAgpSlots int,
@intPCI int,
@bitWSound bit,
@bitWLan bit,
@bitWVideo bit,
@dtmAcquired datetime,
@stat bit output,
@intFSB int) as
if not exists(select strPcName from tblBoards where strPcName=@strPcName)
begin
insert into tblBoards
(BrandID, strPcName, bitAccounted,
dtmAccounted, dtmWarrantyStart,
dtmWarrantyEnd, --strDescription,
intStatus,
ModelNo, intMemorySlots, intMemSlotTaken,
intAgpSlots, intPCI, bitWLan,
bitWVideo, dtmAcquired,intFSB,bitWSound)

values

(@BrandID,@strPcName,@bitAccounted,
@dtmAccounted,@dtmWarrantyStart,
@dtmWarrantyEnd,--@strDescription,
@intStatus,
@ModelNo,@intMemorySlots,@intMemSlotTaken,
@intAgpSlots,@intPCI,@bitWLan,
@bitWVideo,@dtmAcquired,@intFSB,@bitWSound)
end
else
begin
set @stat=1
end

The table is also designed to accept nulls on that field but still same error occured.

Please help

View 1 Replies View Related

Passing Int Parameter To Stored Procedure Question.

Jul 1, 2005

Hi all,
I had created a stored procedure "DeleteRow" that can receive a parameter "recordID" from the calling function, however, I received a error msg "Procedure or function deleteRow has too many arguments specified." when run the  C# code.My code is showing below---------------------------------- --------------------thisConnection.Open();SqlParameter param = DeleteRow.Parameters.Add("@recordI D", SqlDbType.Int, 4); param.Value = key;SqlDataReader reader = DeleteRow.ExecuteReader(CommandBeh avior.CloseConnection) //program stop after runing this lineThe stored procedure code which is showing below:----------------------------------------------------------------CREATE PROCEDURE dbo.deleteRow @recordID INT AS DELETE FROM ShoppingCart WHERE RecordID = @recordIDCan anyone give me some ideal why this happen. Thank alot.wing

View 3 Replies View Related

Passing Fields As Parameter Of Stored Procedure

Jul 6, 2000

Does anyone know how to pass a list of fields into a stored procedure and then use those fields in a select statement. Here's what I'm trying to do.

I have a front end application that allows the user to pick the fields they want return as a record set. Currently, all this is being done in the application. But, I'd like SQL to do it, if it's possible.


I'd pass the following into a stored procedure.

Set @Fields = "First, Last, ID" -- This is done in the application

Exec usp_return @Fields

Obviously, the following fails stored procedure doesn't work ...

CREATE PROCEDURE @FIELDS varchar(255) AS

SELECT @FIELDS FROM MY_TABLE

~~~~~~~~~~~~~~~

Any ideas?

MAPMAN

View 1 Replies View Related

Passing A Stored Procedure Parameter Into An IN Clause

Jul 30, 2007

Hi All :)

I have a stored procedure which, initially, I had passed a single parameter into a WHERE clause (e.g ...WHERE CustomerCode = @CustCode). The parameter is passed using a DECommand object in VB6.

I now require the sp to return values for more than one customer and would like to use an IN clause (e.g ...WHERE CustomerCode IN(@CustCode). I know I could create multiple parameters (e.g. ...WHERE CustomerCode in (@CustCode1, @CustCode2,...etc), but do not want to limit the number of customers.

If I set CustCode to be KA1001, everything works fine. If I set CustCode to be KA1001, KA1002 it does not return any records.

I think the problem is in the way SQL Server concatenates the stored procedure before execution. Is what I am attempting to do possible? Is there any particular format I need to set the string parameter to? I've tried:

KA1001', 'KA1002 (in the hope SQL Server just puts single quotes either side of the string)

and

'KA1001', 'KA1002'

Both fail :(

Any ideas?

Regards

Xo

View 11 Replies View Related

STORED PROCEDURE - Passing Table Name As A Parameter

Nov 29, 2005

I am trying to develop a stored procedure for an existing application thathas data stored in numerous tables, each with the same set of columns. Themain columns are Time and Value. There are literally hundreds of thesetables that are storing values at one minute intervals. I need to calculatethe value at the end of the current hour for any table. I am a little newto SQL Server, but I have some experience with other RDBMS.I get an error from SQL Server, complaining about needing to declare@TableName in the body of the procedure. Is there a better way to referencea table?SteveHere is the SQL for creating the procedure:IF EXISTS(SELECTROUTINE_NAMEFROMINFORMATION_SCHEMA.ROUTINESWHEREROUTINE_TYPE='PROCEDURE'ANDROUTINE_NAME='udp_End_of_Hour_Estimate')DROP PROCEDURE udp_End_of_Hour_EstimateGOCREATE PROCEDURE udp_End_of_Hour_Estimate@TableName VarCharASDECLARE @CurrentTime DateTimeSET @CurrentTime = GetDate()SELECT(SELECTSum(Value)* DatePart(mi,@CurrentTime)/60 AS EmissonsFROM@TableNameWHERETime BETWEENDateAdd(mi,-DatePart(mi,@CurrentTime),@CurrentTime)AND@CurrentTime)+(SELECTAvg(Value)* (60-DatePart(mi,@CurrentTime))/60 AS EmissionsFROM@TableNameWHERETime BETWEENDateAdd(mi,-10,@CurrentTime)AND@CurrentTime)

View 15 Replies View Related

Passing Column Name As Parameter To A Stored Procedure

Jul 20, 2005

Hi!I want to pass a column name, sometimes a table name, to a storedprocedure, but it didn't work. I tried to define the data type aschar, vachar, nchar, text, but the result were same. Any one know howto get it work?Thanks a lot!!Saiyou

View 1 Replies View Related

Passing Multi Value Parameter To Stored Procedure

Aug 7, 2007

I wrote a Stored Procedure spMultiValue which takes Multi Value String Parameter "Month". The stored procedure uses a function which returns a table. when I Execute the stored procedure from SQL Server 2005 with input "January,February,March" everything works fine.
In the dataset , I set command type as Text and typed the following statement.
EXEC spMultiValue " & Parameters!Month.Value &"
its not returning anything.
can anyone tell me how to pass multivalue parameter to stored procedure.
Thanks for your help.

View 2 Replies View Related

Passing Parameter To Stored Procedure From Win Appln

Jan 23, 2008

which is the effective methods of passing more then one parameter to the sql stored procedure from your vb.net windows application

View 3 Replies View Related

Passing Like Criteria In Parameter To SQL Server Stored Procedure

Dec 13, 2006

Hello All,
 I was hoping that someone had some wise words of wisdom/experience on this.  Any assistance appreciated... feel free to direct me to a more efficient way... but I'd prefer to keep using a stored proc.
  I'm trying to pass the selected value of a dropdownlist as a stored procedure parameter but keep running into format conversion  errors but I am able to test the query successfully in the SQLDatasource.  What I would like to do is this: select * from tblPerson where lastnames Like "A%" . Where I pass the criteria after Like.   I have the values of the drop down list as "%", "A%", "B%", ....
I've been successfully configuring all of the other params, which includes another dropdown list (values bound to a lookup table also)... but am stuck on the above...
 Thank you for any assistance,
 

View 3 Replies View Related

Passing Table Name And Order By Parameter To Stored Procedure

Jul 27, 2007

can i pass the name of the table and the "order by" column name to stored procedure?
 i tried the simple way
(@tablename varchar and then "select * from @tablename)
but i get error massesges. the same for order by...
what is the right syntex for this task?

View 2 Replies View Related

Passing Parameter To Stored Procedure Call Within SqlDataSource

Aug 3, 2007

I'm trying to create a Grid View from a stored procedure call, but whenever I try and test the query within web developer it says I did not supply the input parameter even though I am.I've run the stored procedure from a regular ASP page and it works fine when I pass a parameter. I am new to dotNET and visual web developer, anybody else had any luck with this or know what I'm doing wrong? My connection is fine because when I run a query without the parameter it works fine.Here is the code that is generated from web developer 2008 if that helps any...... <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:IMS3TestConnectionString %>" ProviderName="<%$ ConnectionStrings:IMS3TestConnectionString.ProviderName %>" SelectCommand="usp_getListColumns" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:querystringparameter DefaultValue="0" Name="FormID" QueryStringField="FormID" Type="Int32" /> </SelectParameters> </asp:SqlDataSource>   

View 5 Replies View Related

Passing A Parameter For Consumption Of IN Keyword In A Stored Procedure

Nov 14, 2007



Hi all, what's the recommended way of passing a parameter than can be consumed properly by a SQL statement using the IN keyword.

For Example:

proc Test @Param varchar(200)
as
begin

SELECT FieldA, FieldB
FROM TABLE
WHERE FieldA IN (@Param)

end



An option is making the query into a string
strSQL = "SELECT FieldA, FieldB FROM TABLE WHERE FieldA IN (" + @Param ")"

then executing the string, but I was wondering if there is a more effective way?

Thanks

View 5 Replies View Related

SQL Server 2012 :: Passing Date Parameter To Stored Procedure?

Oct 9, 2014

I am trying to pass a date parameter to a stored procedure.

I pass the actual date as a string but keep getting errors that the string variable cannot be converted to a date whatever I do.

Basically, this works:

DECLARE @DDate DATETIME
SET @DDate = convert(datetime, '2004-01-01',101 )

But this returns an error:

DECLARE @strdate VARCHAR
SET @strdate = '2004-01-01'
DECLARE @DDate DATETIME
SET @DDate = convert(datetime, @strdate,101 )

What am I doing wrong?

View 8 Replies View Related

Transact SQL :: Passing (comma Delimited) Parameter To Stored Procedure?

Aug 4, 2015

I am simplifying things here.  Basically I have:

SELECT ......... (my selection criteria)
    where Id in (@Haulers);

@Haulers is supposed to be a comma delimited list.   What do I need to do to make it filter correctly?  

View 13 Replies View Related

Passing SSIS Package Variable To Stored Procedure As Parameter

Feb 25, 2008



I've created a varible timeStamp that I want to feed into a stored procedure but I'm not having any luck. I'm sure its a simple SSIS 101 problem that I can't see or I may be using the wrong syntax

in Execute SQL Task Editor I have
conn type -- ole db
connection -- some server
sql source type -- direct input
sql statement -- exec testStoredProc @timeStamp = ?

if I put a value direclty into the statement it works just fine: exec testStoredProc '02-25-2008'

This is the syntax I found to execute the procedure, I don't udnerstand few things about it.

1. why when I try to run it it changes it to exec testStoredProc @timeStamp = ? with error: EXEC construct or statement is not supported , followed by erro: no value given for one or more requreid parameters.

2. I tired using SQL commands exec testStoredProc @timeStamp and exec testStoredProc timeStamp but nothing happens. Just an error saying unable to convert varchar to datetime

3. Also from SRS I usually have to point the timeStamp to @timeStamp and I dont know how to do that here I thought it was part of the parameter mapping but I can't figure out what the parameter name and parameter size should be; size defaults to -1.

Thank you, please help.

View 2 Replies View Related

Passing Parameter Values To Full Text Search Stored Procedure

Oct 24, 2006

I thought this would be quite simple but can't find the correct syntax:ALTER Procedure usp_Product_Search_Artist_ProductTitle

(@ProductTitle varchar(255))

AS

SELECT ProductTitle

FROM tbl_Product


WHERE CONTAINS(dbo.tbl_Product.ProductTitle, @ProductTitle)
   My problem is that if I pass "LCD Flat Screen" as teh @ProductTitle parameter the query falls over as it contains spaces. Guess i'm looking for something like: WHERE CONTAINS(dbo.tbl_Product.ProductTitle, '"' + @ProductTitle + "'")Thanks in advance.R  

View 4 Replies View Related

Bit Type Parameter For Stored Procedure

Apr 19, 2004

I 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?

View 9 Replies View Related

Stored Procedure Parameter Without Type

May 23, 2006

Hi 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.

View 5 Replies View Related

T-SQL (SS2K8) :: Passing Multiple Parameters With Table Valued Parameter To Stored Procedure?

May 21, 2014

Can we Pass table valued parameters and normal params like integer,varchar etc..to a single stored procedure?

View 1 Replies View Related

Is It Possible To Capture An OUT Type Parameter From A PL/SQL Stored Procedure?

Dec 19, 2007

When 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 Related

Syntax To Pass A Parameter Of Type Nvarchar(max) To Stored Procedure

Dec 26, 2007

I 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)

View 1 Replies View Related

RS 2005: Stored Procedure With Parameter It Runs In The Data Tab But The Report Parameter Is Not Passed To It

Feb 19, 2007

Hello,
since a couple of days I'm fighting with RS 2005 and the Stored Procedure.

I have to display the result of a parameterized query and I created a SP that based in the parameter does something:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [schema].[spCreateReportTest]
@Name nvarchar(20)= ''

AS
BEGIN

declare @slqSelectQuery nvarchar(MAX);

SET NOCOUNT ON
set @slqSelectQuery = N'SELECT field1,field2,field3 from table'
if (@Name <> '')
begin
set @slqSelectQuery = @slqSelectQuery + ' where field2=''' + @Name + ''''
end
EXEC sp_executesql @slqSelectQuery
end

Inside my business Intelligence Project I created:
-the shared data source with the connection String
- a data set :
CommandType = Stored Procedure
Query String = schema.spCreateReportTest
When I run the Query by mean of the "!" icon, the parameter is Prompted and based on the value I provide the proper result set is displayed.

Now I move to "Layout" and my undertanding is that I have to create a report Paramater which values is passed to the SP's parameter...
So inside"Layout" tab, I added the parameter: Name
allow blank value is checked and is non-queried

the problem is that when I move to Preview -> I set the value into the parameter field automatically created but when I click on "View Report" nothing has been generated!!

What is wrong? What I forgot??

Thankx for any help!
Marina B.





View 3 Replies View Related

SQL 2012 :: SSIS Passing Parameters To Stored Procedure That Changes Based On The Data Being Passed?

Jun 23, 2015

Using the following:

SQL Server: SQL Server 2012
Visual Studio 2012

I have created an SSIS package where I have added an Execute SQL Task to run an existing stored procedure in my SQL database.

General Tab:

Result Set: None
Connection Type: OLE DB
SourceType: Direct Input
IsQueryStoredProcedure: False (this is greyed out and cannot be changed)
Bypass Prepare: True
SQL Statement: EXEC FL_CUSTOM_sp_ml_location_load ?, ?;

Parameter Mapping:

Variable Name Direction Data Type Prmtr Name Prmtr Size
User: system_cd Input NVARCHAR 0 10
User: location_type_cd Input NVARCHAR 1 10

Variables:

location_type_cd - Data type - string; Value - Store (this is static)
system_cd - Data type - string - ??????
The system code changes based on the system field for each record in the load table

Sample Data:

SysStr # Str_Nm
3 7421Store1
3 7454Store2
1815061Store3
1815063Store4
1615064Store5
1615065Store6
1615066Store7
7725155Store8

STORED PROCEDURE: The stored procedure takes data from a load table and inserts it into another table:

Stored procedure variables:
ALTER PROCEDURE [dbo].[sp_ml_location_load]
(@system_cd nvarchar(10), @location_type_cd nvarchar(10))
AS
BEGIN .....................

This is an example of what I want to accomplish: I need to be able to group all system 3 records, then pass 3 as the parameter for system_cd, run the stored procedure for those records, then group all system 18 records, then pass 18 as the parameter for system_cd, run the stored procedure for those records and keep doing this for each different system in the table until all records are processed.

I am not sure how or if it can be done to pass the system parameter to the stored procedure based on the system # in the sys field of the data.

View 6 Replies View Related

How To Declare A Procedure Parameter Type To Match A Referenced Table Colum Type

Dec 14, 2007

I like to define my procedure parameter type to match a referenced table colum type,
similar to PL/SQL "table.column%type" notation.
That way, when the table column is changes, I would not have to change my stored proc.
Any suggestion?

View 1 Replies View Related

Data Type Conversion In Stored Procedure

Aug 2, 2006

Hi,

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

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved