Using Table Variable As Input To Function Or Procedure

How To Input A DOS Variable Into A Stored Procedure
I am trying to Execute a Stored Procedure using Call OSQL from a .bat file, and passing a DOS variable to the Stored Procedure.

The DOS variable is established in the DOS command...

set SERVERJOB=JOBNAME

I have tried...

EXEC sp_procedure %SERVERJOB%

With this, I get an error...

sg 170, Level 15, State 1, Server ABCDEFGH, Line 20
Line 20: Incorrect syntax near '%'.

If I put the variable name in quotes on the EXEC statement above, the value used is the variable name, %SERVERJOB% itself rather than the value the variable was set to, which would be JOBNAME in the above example.

Any ideas??? Thanks!

View Replies !   View Related

---

Function That Take A Table As Input Parameter
hi all
i am using VS 2005 with SQL Server 2005 and i faced a problem that need to be solved urgently...
i want to make a function that take a table as input parameter which is the output of a stored procedure (Record set)...
first i found that to make w table be as input parameter you must create type of that table first but i found that sql server 2005 doesn't have the 'table' as a type...
please any help will be appreciated
thanks in advance

View Replies !   View Related

---

Function That Take A Table As Input Parameter
i want to make a function that will take a table as input parameter. this table will be the output of a stored procedure. while i were writing the function i have an error and when i read about it i found that i can not send a table as input parameter to a function till i create a new TYPE of this table with its columns and data types as UDT but i found that sql server 2005 does not support the type 'table'...
my question now is it possible technically to make this function? is it possible to write something like that :
 
SELECT dbo.MyFunction(exec dbo.MystoredProc)
 
and in my function i am using CLR-Integration as this :
 create function MyFunction
(
@TempTable table
(
ContractID int,
ContractNumber nvarchar(20),
Name_En nvarchar(80),
Name_Ar nvarchar(80),
ContractAmount money,
CurrencyID nchar(3),
DateStart smalldatetime,
DateEnd smalldatetime,
Currency_En nvarchar(30),
Currency_Ar nvarchar(30)
)
)
returns table( ContractNumber nvarchar(20),
Name_En nvarchar(80),
Name_Ar nvarchar(80),
ContractAmount decimal,
Currency_En nvarchar(30),
Currency_Ar nvarchar(30),
[Year] int,
[Month] int,
DomesticAmount decimal
)
as external name [AssemblyName].[PathOfTheFunctionInTheAssembly].[FunctionNameInAssembly]
 
please help me in this code as i need it urgently...
thanks in advance,
best regards,
Moustafa

View Replies !   View Related

---

Dynamic OLE DB Table Source From Variable Not Seeing Input Columns
I am building an SSIS package that loops through a table in SQL Server and dynamically builds a select statement that i would like to use as an ole db source.  I have been having a difficult time with this as the select statement that i am generating is over 200,000 characters long so using an sql variable is out of the question. 

I ended up placing the select statement into a table where each row of the table represents a piece of the select.  I then use an execute_sql task that selects the entire rowset from this table into a variable object.  I then use a for each loop to shred the variable and concatenate it into on big string variable called user:: sql_statement that is my select.

After setting up the loop and testing to see if the user:: sql_statement variable populates correctly i then added a data flow transfer with an ole db source and destination. I then go into the advanced editor for the source and set it to accept an sql statement from a variable and use my user:: sql_statement variable.  I was forced to set validate external metadata option to false to avoid an error since there is no way to validate the columns until the for each loop runs during run time.

Now thats all fine and good but what is causing my problem is that during run time, when the package gets to the data flow task, the select statement doesn't seem to be populating the input columns of the data source.  I have been searching to no avail on a way to tell the data source to update the input columns but every time it gets there, the package bombs out telling me the ole db source has no available output columns.

Specifically the error i get is :
[DTS.Pipeline] Error: "output "OLE DB Source Output" (6616)" contains no output columns. An asynchronous output must contain output columns.

Any help with this would be much appreciated. 

View Replies !   View Related

---

Stored Procedure That Uses Input From Another Table
Hi,    I need to be able to create a Stored Procedure that gets its information based on dates stored in another table.Does anyone have an idea on how I can acheive this??Regards..Peter.

View Replies !   View Related

---

Table Name As An Input Parameter To A Procedure
Hi
I have this procedure it is creating the proc but when I execute it gives error
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@ID".
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@nextCode".

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetNextAction]

(
@Code char(10),
@Track varchar(30)

)
AS
BEGIN

SET NOCOUNT ON;
Declare @ID int;
DECLARE @SQL1 VARCHAR(2000)
SET @SQL1='Select @ID = Sequence from'+' '+ @Track+ ' where Code=@Code'
EXEC(@SQL1);
Declare @nextCode varchar;
DECLARE @SQL2 VARCHAR(2000)
SET @SQL2 ='Select @nextCode= Code from '+' '+ @Track+ ' where sequence =(@ID+1)'
EXEC(@SQL2);
Declare @NextAction varchar(30);
Select @NextAction= nextAction from [dbo].[CaseStage] where Code=@nextCode;
Select @NextAction;


END
GO
Can someone correct me here
Thanks

View Replies !   View Related

---

Stored Procedure With Input Going To A Temp Table
Could someone help me get this stored procedure to work? I want to give the stored procedure a long list of departments and have them added to a temp table. This only gets the first dept. in the temp table. I'm confused. Open to other suggestions, but want to use a 1col temp table to hold the depts.

After this is done, an SQL query is run using the temp table.
Input for test:
--csi_crystal_xxxx "pc9xp,pc8,pc7,pc6,pc6543,pc945678"
--select * from ##CrystalGetCosts

create procedure csi_crystal_xxxx

@DeptResp varchar(4000)
AS
SET NOCOUNT ON
DECLARE @SQL varchar(8000)
DECLARE @Dept varchar(10)
DECLARE @iLen int
DECLARE @iPtr int
DECLARE @iEnd int

If Exists (Select name, Type From [tempdb]..[sysobjects]
where name = '##CrystalGetCosts' And Type = 'U')
Drop table ##CrystalGetCosts

CREATE TABLE ##CrystalGetCosts (Dept_Resp_No varchar(10))
Set @iLen=Len(@DeptResp)
Set @iPtr = 1
While @iPtr < @iLen
BEGIN
SET @iEND = charindex(',',@DeptResp,@iPtr)
Set @Dept= Substring (@DeptResp,@iPtr,@iEnd-1)
INSERT INTO ##CrystalGetCosts Values (@Dept)
Set @iPtr = @iEnd + @iLen
END

View Replies !   View Related

---

Passing A Table As An Input To Stored Procedure
Hi everyone,

Is that possible to passing a table as an input to Stored Procedure?

Thanks in advance

View Replies !   View Related

---

Indexes On Table Variable Of Table Valued Function
Hi there,

Can someone tell me if it is possible to add an index to a Table variable that is declare as part of a table valued function ? I've tried the following but I can't get it to work.

ALTER FUNCTION dbo.fnSearch_GetJobsByOccurrence
(
@param1 int,
@param2 int
)
RETURNS @Result TABLE (resultcol1 int, resultcol2 int)
AS
BEGIN

CREATE INDEX resultcol2_ind ON @Result

-- do some other stuff

RETURN
END

View Replies !   View Related

---

Table-valued Function Run Once For Each Row In A Table Variable.
I have a stored produre.  Inside this stored procedure I have table variable with one column.  Once the table variable is populated with rows, I would like to pass each value in the table, into a table-valued function.  The table-valued function may return any number of rows.  I would like all the rows the TVF returns to be returned from the stored procedure as a single result set.  I would also like to do this without defining a table variable to hold the results of the table-value function.
 



Code Snippet

declare @IdTable table
(
 EmployeeId nvarchar( 16 ) not null
)
insert into @IdTable
select EmployeeNumber from Employees
 
/*
I need to run this query for every EmployeeId value in @IdTable and return the results from the stored proc as a single result set.
*/
select * from fn_GetEmployeeById( EmployeeId )

 
 



 
Any help is very much appreciated.
Andrew
 

View Replies !   View Related

---

Table-valued Function Into A @table Variable
In my stored procedure i have a multi-valued varchar(max) parameter and I wrote a table-valued function that takes the varchar(max) and return a table back to the stored procedure where i inserted into a @table. Just wondering is there a better and faster way of doing this?




ALTER   PROCEDURE [dbo].[rpt]

(


@CourtIDs as nvarchar(MAX) -- @CourtIDs = '1231,3432,1234,3421'

)
AS


--split CourtIDs into a table
DECLARE @tbCourtIDs     table(CourtID int NOT NULL PRIMARY KEY)
INSERT INTO @tbCourtIDs
select * from dbo.Split(@CourtIDs, ',')

View Replies !   View Related

---

Make A Select From A Clr Function To A Table Variable
Hi,
is it somehow possible to make a Select-Statement from a Clr-Funktion to a table variable??
 
Regards
Pamelia

View Replies !   View Related

---

Passing Variable To Table Function In Join
Hello, thanks in advance for reading this. I am having difficulty trying to get a statement to work.

There is a MAIN table:
ItemNo int identity(1,0),
ItemType tinyint

There is a WETPAINT table:
ItemNo int,
Color varchar(20)

There is a DRYPAINT table:
ItemNo int,
Color varchar(20)

Now, what I want to do is JOIN the MAIN table to either the WETPAINT table or the DRYPAINT table depending on the value of MAIN.ItemType

So I created a table function called getTable:

CREATE FUNCTION [dbo].[gettable]
(
@ItemType int = 1
)
RETURNS
@thistable TABLE
(
Color varchar(20)

)
AS
BEGIN
if @ItemType = 1
insert into @thistable (color) select color from WETPAINT
if @ItemType = 2
insert into @thistable (color) select color from DRYPAINT
RETURN
END

This is all fine and dandy if I iterate through the MAIN table one row at a time, but how can I JOIN the tables, like:

SELECT MAIN.ItemNo, a.Color
FROM MAIN
INNER JOIN gettable(Main.ItemNo) as a
ON a.ItemNo = MAIN.ItemNo

Obviously, there is more than one field in the DRYPAINT and WETPAINT tables, and there is a need to have both tables instead of combining them into one.

Any help in how to create a table alias by passing a value from the select statement would be greatly appreciated! Thanks again.

PS -- I am trying to create a view with this, so I can't use variables and iterate through the MAIN table one row at a time.

View Replies !   View Related

---

What Is The Difference Between: A Table Create Using Table Variable And Using # Temporary Table In Stored Procedure
 which is more efficient...which takes less memory...how is the memory allocation done for both the types.

View Replies !   View Related

---

Problem Passing A Variable Into A Table-valued Function
Hi,

i am encountering a problem in a stored procedure when a pass a variable value into a table-valued function. The table-valued function is named getCurrentDriver and has 1 attribute: car-ID.

The syntax is as follows:

select car.id, car.licenceNumber, car.brand, car.model,
(select driverName from getCurrentDriver(car.id)) as driverName
from car

When I try to compile I get following error on the line of the function:
Incorrect syntax near '.'

The database version is SQL Server 2000 SP3.

What am I doing wrong? Is there a workaround for this error?

View Replies !   View Related

---

Passing Table Variable To Stored Proc / Function
Hi all,
Is it possible to pass a table variable to a Stored proc or a function?
If it is can you give me the sentax.

TIA,

View Replies !   View Related

---

How Can I Assign Data Returned From A Stored Procedure Into The Return Table Of A Table Valued Function
Here is the scenario,
I have 2 stored procedures, SP1 and SP2

SP1 has the following code:

declare @tmp as varchar(300)
set @tmp = 'SELECT * FROM
OPENROWSET ( ''SQLOLEDB'', ''SERVER=.;Trusted_Connection=yes'',
''SET FMTONLY OFF EXEC ' + db_name() + '..StoredProcedure'' )'

EXEC (@tmp)

SP2 has the following code:

SELECT *
FROM SP1 (which won't work because SP1 is a stored procedure. A view, a table valued function, or a temporary table must be used for this)

Views - can't use a view because they don't allow dynamic sql and the db_name() in the OPENROWSET function must be used.
Temp Tables - can't use these because it would cause a large hit on system performance due to the frequency SP2 and others like it will be used.
Functions - My last resort is to use a table valued function as shown:

FUNCTION MyFunction
( )
RETURNS @retTable
(
@Field1 int,
@Field2 varchar(50)
)
AS
BEGIN
-- the problem here is that I need to call SP1 and assign it's resulting data into the
-- @retTable variable

-- this statement is incorrect, but it's meaning is my goal
INSERT @retTableSELECT *FROM SP1

RETURN
END

View Replies !   View Related

---

Variable Table Name In A Stored Procedure
Hello Forum !I want to have the tablename "dbo.Enbxxxx" as an additional parameterfor a procedure like this:ALTER Procedure prcSucheUNR(@UNR int)Asset nocount onSELECT ABRUFNR,UNR,STICHTAG,Datum,InhaltINTO #temp FROM dbo.Enb WHERE UNR = @UNRFor some reason:@tablename varchar(11),and: INTO #temp FROM @tablename WHERE .... does not work. :-(I get the following syntax Error:Zeile 33: Falsche Syntax in der Nähe von '@tablename'.I'am sure it is possible but i don't know how.Greetings

View Replies !   View Related

---

Table Variable In Stored Procedure - Am I Doing This Right?
 

I am working on an mailing list program and I am trying to create a preview option for the sender (to preview their message prior to sending)
 
I am new to .net and sql but I thought it might be best to do this with table variable inside a stored procedure.. I just do not know how to insert the data into the table variable and select that data at the same time, to return it back to a preview.aspx page.
 
Please forgive my ignorance..
 
here is my stored procedure..

ALTER PROCEDURE [dbo].[proc_MessagePreview]

@Subject varchar(150),

@EmailMessage varchar(max)

AS

BEGIN

DECLARE @MessagePreview Table(Subject varchar(150), EmailMessage varchar(max))

INSERT INTO @MessagePreview (Subject, EmailMessage)

VALUES (@Subject, @EmailMessage)

SELECT Subject, EmailMessage FROM @MessagePreview

END

 
any suggestion or help to point me in the right direction is appreciated, thanks!

View Replies !   View Related

---

T-SQL, Stored Procedure Using A Variable Table Name
I need to use a variable for a table name, pass it to a stored procedure that extracts data, and then store the result of the sp in another variable.
 
For example, earlier in the program I construct the table name using this statement:
 
@tablename = 'OC_TEMPLATE_' + @FORM.  The resulting table name may by something like:  OC_TEMPLATE_101
 
Now I need to use this table name in a statement like this:
 
SELECT ITEM FROM @TABLENAME WHERE DEX = @REC
 
If I use code like this, I can get a result, but I can't store it in a variable:
 
EXECUTE ('SELECT ITEM FROM  ' + @TABLENAME + ' WHERE DEX = ' + @REC)
 
Running this give me a value of 309 in the results pane in Management Studio.  309 is the value I want, but I want to now store that in a variable called @ITEM.
 
I'm trying to run this as a stored procedure where I pass @tablename and @rec into the proc and I want to return @item as an output parameter with the value of 309 (in this case) in @item.  I can't figure out how to configure the proc to give me the output parameter.
 
Any ideas?
 
T

View Replies !   View Related

---

Compare The Value Of A Variable With Previous Variable From A Function ,reset The Counter When Val Changes
I am in the middle of taking course 2073B €“ Programming a Microsoft SQL Server 2000 Database. I noticed that in Module9: Implementing User-Defined Functions exercise 2, page 25; step 2 is not returning the correct answer.
 
Select employeeid,name,title,mgremployeeid from dbo.fn_findreports(2)
 
It returns manager id for both 2 and 5 and I think it should just return the results only for manager id 2.  The query results for step 1 is correct but not for step 2.
 
Somewhere in the code I think it should compare the inemployeeid with the previous inemployeeid, and then add a counter. If the two inemployeeid are not the same then reset the counter.  Then maybe add an if statement or a case statement.  Can you help with the logic?  Thanks!
 
Here is the code of the function in the book:
 
/*
**       fn_FindReports.sql
**
**  This multi-statement table-valued user-defined
**  function takes an EmplyeeID number as its parameter
**  and provides information about all employees who
**  report to that person.
*/
USE ClassNorthwind
GO
/*
**  As a multi-statement table-valued user-defined
**  function it starts with the function name,
**  input parameter definition and defines the output
**  table.
*/
CREATE FUNCTION fn_FindReports (@InEmployeeID char(5))
RETURNS @reports TABLE
  (EmployeeID char(5) PRIMARY KEY,
  Name nvarchar(40) NOT NULL,
  Title nvarchar(30),
  MgrEmployeeID int,
  processed tinyint default 0)
-- Returns a result set that lists all the employees who
-- report to a given employee directly or indirectly
AS
BEGIN
 DECLARE @RowsAdded int
 -- Initialize @reports with direct reports of the given employee
 INSERT @reports
  SELECT EmployeeID, Name = FirstName + ' ' + LastName, Title, ReportsTo, 0
  FROM EMPLOYEES
  WHERE ReportsTo = @InEmployeeID
 SET @RowsAdded = @@rowcount
 -- While new employees were added in the previous iteration
 WHILE @RowsAdded > 0
 BEGIN
  -- Mark all employee records whose direct reports are going to be
  -- found in this iteration
  UPDATE @reports
  SET processed = 1
  WHERE processed = 0
  
  -- Insert employees who report to employees marked 1
  INSERT @reports
   SELECT e.EmployeeID, Name = FirstName + ' ' + LastName , e.Title, e.ReportsTo, 0
   FROM employees e, @reports r
   WHERE  e.ReportsTo = r.EmployeeID
   AND r.processed = 1
  SET @RowsAdded = @@rowcount
  -- Mark all employee records whose direct reports have been
  -- found in this iteration
  UPDATE @reports
  SET processed = 2
  WHERE processed = 1
 END
RETURN -- Provides the value of @reports as the result
END
GO
 

View Replies !   View Related

---

Parsing An Input Variable
Does anyone know how to parse an input variable to a stored proc? For example, if I have an input variable that is: 'ford,chevy,pontiac' how do I parse through this variable and handle each value independently?

Since I do not know how many values may exists I can't do it based on a set amount of input variables.

View Replies !   View Related

---

How To Set Input Xml Name And Folder From Variable
Hey Guys,

Heres my issue. please help

I have xml files and xslt files are in seperate folders. and each xml file has its own xslt. I've got the for loop riunning which loops thro the metadata table. This table has the file and folder localtions of xml and its corresponding xslt.
I'm using the transform xml task in control fllow. i dont see an option to set input xml name and folder from Variable in transform xml component properties? It seems to expect fully qualified path. is there an alternative? or what am i doing wrong?

View Replies !   View Related

---

Passing Variable Table Names To Stored Procedure
I need to execute a stored procedure which selects all columns from the passed table. The table used is a variable.

Select * from @Passedtablename. This won't work. Any insights.

View Replies !   View Related

---

Can't TABLE Variable Be Used In EXEC Statement In Stored Procedure
Hi
 
I've used a temporary table in the stored procedure
 
I've created it as DECLARE @Temp TABLE (id INT)
 
in select clause I've used this temp table through the joins..
 
But I've also used a varchar variable to store my criteria...
 
which I'm building in the stored procedure
 
so inorder to use it in the where clause I used
 
EXEC ('SELECT ....................


FROM @Temp T

LEFT OUTER JOIN ...
LEFT OUTER JOIN ...
WHERE ' + @Criteria )

 
Unfortunately this is not working.
Giving the errror
 

Must declare the variable '@TempT'.
 
I had to use Temporary table using #, which I feel is really waste of memory...
 
Is there a way by which I can use my Table variable in the EXEC statement.
 
Thanks In advance
 
 

View Replies !   View Related

---

Variable Number Of Input Columns
We are trying to use the Import/export wizard to load a text file to a SQL Server 2005 database. The input file has a variable number of columns per row. For example, the first row has 3 columns, the second has 7, the third has 3, etc. The number of columns varies from 2 to 9 in the input file. The columns are separated by an uptick (`) and the rows are terminated by {CR}{LF}. We are using code page 1252. On processing, the wizard reads the first row (with 3 columns) ok, but then assumes all the other rows have 3 columns and parses the rows accordingly, ignoring the field and row terminators.

The process worked fine with SQL Server 2000. Is there some setting that we are missing, or some configuration on the database that we should be checking?

 

Thank You

 

View Replies !   View Related

---

Using Input Variable With .NETODBC Provider
 

In my data flow task, I use a data reader source to get data from our 400.  Currently, I have a simple sql command that is pulling data from a file qualifying by date, which is hard-coded.  I would like to pass in a parameter which has the date in it.  How can I do this?  Thanks!

View Replies !   View Related

---

&&"Must Declare The Scalar Variable&&" In Table-valued Function
Hi, I'm having trouble with this multi-statement table-valued function:

ALTER FUNCTION MakeArDetail
(
    -- Add the parameters for the function here
    @dateStart DATETIME,
    @dateEnd DATETIME
)
RETURNS @arDetail TABLE 
(   
     Insurer VARCHAR(50),
     NABP INT DEFAULT 0,
     Claim MONEY DEFAULT 0,
     Payment MONEY DEFAULT 0,
     NumRx CHAR(7),
     PatientName VARCHAR(50),
     Paid030 MONEY DEFAULT 0,
     Paid3160 MONEY DEFAULT 0,
     Paid6190 MONEY DEFAULT 0,
     Paid91120 MONEY DEFAULT 0,
     Paid121 MONEY DEFAULT 0
)
AS
BEGIN
DECLARE @arTemp TABLE
(   
     Insurer VARCHAR(50),
     NABP INT DEFAULT 0,
     Claim MONEY DEFAULT 0,
     Payment MONEY DEFAULT 0,
     NumRx CHAR(7),
     PatientName VARCHAR(50),
     Paid030 MONEY DEFAULT 0,
     Paid3160 MONEY DEFAULT 0,
     Paid6190 MONEY DEFAULT 0,
     Paid91120 MONEY DEFAULT 0,
     Paid121 MONEY DEFAULT 0
)

INSERT INTO @arTemp
   SELECT DISTINCT Insurer,NABP,0,0,NumRx,Patient,0,0,0,0,0 FROM Pims;
UPDATE @arTemp SET Claim =
  (SELECT SUM(Pims.AmtReq)
       FROM Pims
       WHERE Pims.Insurer = @arTemp.Insurer AND
             Pims.NABP = @arTemp.NABP AND
             Pims.NumRx = @arTemp.NumRx
  );

INSERT INTO @arDetail SELECT * FROM @arTemp
RETURN
END
GO

I get
Msg 137, Level 15, State 2, Procedure MakeArDetail, Line 43
Must declare the scalar variable "@arTemp".

I don't understand why SQL thinks @arTemp is a scalar variable which has to be declared.
If I don't include the UPDATE command the thing works.

View Replies !   View Related

---

Input Parameter To Function In SQL Query
I am trying to use a Execute SQL task in which I call a query and get back a scalar value. I THINK I have it set up correctly, yet I am getting a very unhelpful error message of:

Error: 0xC002F210 at Determine Previous Trade Date, Execute SQL Task: Executing the query "SELECT[Supporting].[dbo].[fGetOffsetTradeDate](?, -1) AS [PreviousTradeDate]" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The Parameter Mapping has a single INPUT entry of data type DATE mapped to parameter 0.

The Result Set property (in General) is set to Single Row and there is a single entry in the Result Set config which maps [PreviousTradeDate] to a variable.

Odd thing is, if I replace the ? in the query with a date (say '03/24/2006') everything works fine. This would indicate that my query syntax is fine.

View Replies !   View Related

---

Can A Stored Procedure Called From An Inline Table-Valued Function
Hi,

I'm trying to call a Stored Procedure from a Inline Table-Valued Function. Is it possible? If so can someone please tell me how? And also I would like to call this function from a view. Can it be possible? Any help is highly appreciated. Thanks

View Replies !   View Related

---

Output From DBCC Input Buffer Into Variable
I am writing a scheduled task to
1.grab the details of any processes causing a large blocking chain
2. delete the offending processes

In there any way to grap the output from the DBCC Input Buffer into a variable or directly into a table so that this can be part of the information captured.

Thank you

View Replies !   View Related

---

Web Service Task - Passing Variable As Input
Microsoft says it is possible but I just do not see how. Here is the
link to the help file where it said that variables could be pass as
input to web methods...I do not see the check box they mention on my
IDE.


Any help would be greatly appreciated.


Thanks,
Catherine

View Replies !   View Related

---

How To Use A Variable From The For Loop Container As A Input Parameter To A SP
Hi Everyone:

I have a quick but imp SSIS question. I have a For Each Loop Container, and inside that I wish to add a Execute SQL Task item, so I can call a sp to do some inserts/updates. The ForEachLoop container is looping thru a ADO Object source variable(which is the user variable defined by me, as a FullResultSet). In my Execute SQL Task, I would like to utilize one of the columns from the result set as an input parameter to my Stored procedure. Can someone please advise on how to do this? Please let me know if you have any more questions. I am waiting for a response... Thanks in advance.

 

MA

View Replies !   View Related

---

Standard Input Variable Not Working Properly
Hi,
 
I have an Execute Process Task that calls a batch file.
 
I need to pass in a date value to the batch file.
 
If I enter the value 20071018 in the "Arguments" section, it works fine.
 
However, I use the "Standard Input Variable", User::MyDate (which gets assigned in a previous Execute SQL Task), it bombs. The query I am using returns the date back in the format yyyymmdd. It gets assigned to a string variable, called MyDate.
 
e.g.)
SELECT TOP 1  CONVERT(VARCHAR, MyDate, 112) AS MyDate
FROM myTable
 
which result is mapped to User::MyDate
 
Is there any obvious reason why this doesn't work?
 
Thanks

View Replies !   View Related

---

Creating Views With Variable No. Of Input Tables
Hello,
I need to create a view which will include an additional table every week of our financial year.
Example: Every week we create a table called Sales_YYYY_WW where YYYY is the Year and WW is the Week, so after the first week we have the table Sales_2007_01 and after the second week we have Sales_2007_01 and Sales_2007_02 etc.. These tables are created evey week and don't exist at the start of the financial year.
I'd like to create a view at the beginning of each year to encompass each table evey week as it is created. If all of the tables were there at the start of the year it would be simple:
CREATE VIEW VIEW_SALES_2007
AS
SELECT * from Sales_2007_01
UNION ALL
SELECT * from Sales_2007_02
etc.
But I don't know how to achieve this when the tables don't already exist. I don't want to have to edit the view each week to add in the new table.

Can anyone out there help me?

View Replies !   View Related

---

Calling CLR Stored Procedure From Within A CLR Table-valued Function Giving Errors
We are trying to create a TVF that executes a CLR Stored Procedure we wrote to use the results from the SP and transform them for the purposes of returning to the user as a table.

 




Code Snippet

[SqlFunction ( FillRowMethodName = "FillRow",

TableDefinition = "CustomerID nvarchar(MAX)",

SystemDataAccess = SystemDataAccessKind.Read,

DataAccess = DataAccessKind.Read,

IsDeterministic=false)]

public static IEnumerable GetWishlist () {

using (SqlConnection conn = new SqlConnection ( "Context Connection=true" )) {

List<string> myList = new List<string> ();

conn.Open ();

SqlCommand command = conn.CreateCommand ();

command.CommandText = "GetObject";

command.Parameters.AddWithValue ( "@map", "Item" );

command.CommandType = System.Data.CommandType.StoredProcedure;

using ( SqlDataReader reader = command.ExecuteReader ( System.Data.CommandBehavior.SingleRow )) {

if (reader.Read ()) {

myList.Add ( reader[0] as string );

}

}



return (IEnumerable)myList;

}

}

 

When command.ExecuteReader is called, I am getting an "Object not defined" error.  However, the stored procedure can be used in SQL Management Studio just fine.

 




Code SnippetEXEC GetObject 'Item'

 

Is there some sorf of trick I am missing?

 

Thank you!

 

View Replies !   View Related

---

SqlDataSource.Update With Session Variable As Input Parameter
I'm getting a type mismatch error (currently string, when I think I need Int32 ?) with the code below. I'm having difficultly setting my session variable to type Int32 and having it take up the value of RawCoDeptRowID. When I "Watch" it, it's value remains as "snCoDeptRowID".  Also, not sure if my Update command and it's snCoDeptRowID input parameter is well formed? Any advice would be greatly appreciated. Thank you.
Web Page 1:
Session["snCoDeptRowID"] = Convert.ToInt32 (RawCoDeptRowID);
 
Web Page2:<asp:SqlDataSource ID="SqlDataSource3" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
UpdateCommand="UPDATE [CompanyDepartment] SET [User_Name] = @User_Name, [FirstName] = @FirstName, [LastName] = @LastName, [Company_Name] = @Company_Name, [Department_Name] = @Department_Name WHERE [User_ID] = @snCoDeptRowID ">
<updateparameters>
<asp:parameter Name="User_Name" Type="String" />
<asp:parameter Name="FirstName" Type="String" />
<asp:parameter Name="LastName" Type="String" />
<asp:Controlparameter Name="Company_Name" Type="String" ControlID ="ListBox1" PropertyName ="SelectedValue" />
<asp:Controlparameter Name="Department_Name" Type="String" ControlID ="ListBox2" PropertyName ="SelectedValue" />
<asp:QueryStringParameter Name="User_ID" Type="Int32" QueryStringField ="@snCoDeptRowID" DefaultValue ="@snCoDeptRowID" />
</updateparameters>
</asp:SqlDataSource>

View Replies !   View Related

---

Passing Object Variable As Input Parameter To An Execute SQL Task Query
I've encountered a new problem with an SSIS Pkg  where I have a seq. of Execute SQL tasks. My question are:

1) In the First Execute SQL Task, I want to store a single row result of @@identity type into a User Variable User::LoadID of  What  type. ( I tried using DBNull Type or Object type which works, not with any other type, it but I can't proceed to step 2 )

 

2) Now I want to use this User::LoadID as input  parameter of   What  type for the next  task (I tried using Numeric, Long, DB_Numeric, Decimal, Double none of there work).

 

Please give me solutions for the above two..

 

 

View Replies !   View Related

---

Contact Form - Variable Number Of Input Fields, Store Data As Xml String ?
Im trying to determine the best way to store data gathered from a form that a user will fill out online.  The form is dynamic and is customized at run time based on group-specific criteria.  The end result is a form that might have 3 extra text boxes, 2 extra sets of radio buttons and a freeform textbox, whereas for another group, there might be a slightly different set of input fields.   Now comes the issue of storing this data.  Since the fields can be somewhat dynamic, it could get tricky to define table columns for each possible input field.  So Im considering storing the data as xml.  Has anyone else had to build custom forms and ended up storing the data as xml ?

View Replies !   View Related

---

SSIS: Problem Mapping Global Variables To Stored Procedure. Can't Pass One Variable To Sp And Return Another Variable From Sp.
I'm new to SSIS, but have been programming in SQL and ASP.Net for several years. In Visual Studio 2005 Team Edition I've created an SSIS that imports data from a flat file into the database.  The original process worked, but did not check the creation date of the import file. I've been asked to add logic that will check that date and verify that it's more recent than a value stored in the database before the import process executes.
 
Here are the task steps.


[Execute SQL Task] - Run a stored procedure that checks to see if the import is running. If so, stop execution. Otherwise, proceed to the next step.

[Execute SQL Task] - Log an entry to a table indicating that the import has started.

[Script Task] - Get the create date for the current flat file via the reference provided in the file connection manager. Assign that date to a global value (FileCreateDate) and pass it to the next step.  This works.

[Execute SQL Task] - Compare this file date with the last file create date in the database. This is where the process breaks.  This step depends on 2 variables defined at a global level. The first is FileCreateDate, which gets set in step 3.  The second is a global variable named IsNewFile. That variable needs to be set in this step based on what the stored procedure this step calls finds out on the database.  Precedence constraints direct behavior to the next proper node according to the TRUE/FALSE setting of IsNewFile.


If IsNewFile is FALSE, direct the process to a step that enters a log entry to a table and conclude execution of the SSIS.

If IsNewFile is TRUE, proceed with the import.  There are 5 other subsequent steps that follow this decision, but since those work they are not relevant to this post.
Here is the stored procedure that Step 4 is calling.  You can see that I experimented with using and not using the OUTPUT option.  I really don't care if it returns the value as an OUTPUT or as a field in a recordset.  All I care about is getting that value back from the stored procedure so this node in the decision tree can point the flow in the correct direction.
 

CREATE PROCEDURE [dbo].[p_CheckImportFileCreateDate]

/*

The SSIS package passes the FileCreateDate parameter to this procedure, which then compares that parameter with the date saved in tbl_ImportFileCreateDate.

If the date is newer (or if there is no date), it updates the field in that table and returns a TRUE IsNewFile bit value in a recordset.

Otherwise it returns a FALSE value in the IsNewFile column.

Example:

exec p_CheckImportFileCreateDate 'GL Account Import', '2/27/2008 9:24 AM', 0

*/

@ProcessName varchar(50)

, @FileCreateDate datetime

, @IsNewFile bit OUTPUT

AS

SET NOCOUNT ON

--DECLARE @IsNewFile bit

DECLARE @CreateDateInTable datetime

SELECT @CreateDateInTable = FileCreateDate FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName

IF EXISTS (SELECT ProcessName FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName)

BEGIN

-- The process exists in tbl_ImportFileCreateDate. Compare the create dates.

IF (@FileCreateDate > @CreateDateInTable)

BEGIN

-- This is a newer file date. Update the table and set @IsNewFile to TRUE.

UPDATE tbl_ImportFileCreateDate

SET FileCreateDate = @FileCreateDate

WHERE ProcessName = @ProcessName

SET @IsNewFile = 1

END

ELSE

BEGIN

-- The file date is the same or older.

SET @IsNewFile = 0

END

END

ELSE

BEGIN

-- This is a new process for tbl_ImportFileCreateDate. Add a record to that table and set @IsNewFile to TRUE.

INSERT INTO tbl_ImportFileCreateDate (ProcessName, FileCreateDate)

VALUES (@ProcessName, @FileCreateDate)

SET @IsNewFile = 1

END

SELECT @IsNewFile
 
The relevant Global Variables in the package are defined as follows:
Name : Scope : Date Type : Value
FileCreateDate : (Package Name) : DateType : 1/1/2000
IsNewFile : (Package Name) : Boolean : False
 
Setting the properties in the "Execute SQL Task Editor" has been the difficult part of this.  Here are the settings.
 
General
Name = Compare Last File Create Date
Description = Compares the create date of the current file with a value in tbl_ImportFileCreateDate.
TimeOut = 0
CodePage = 1252
ResultSet = None
ConnectionType = OLE DB
Connection = MyServerDataBase
SQLSourceType = Direct input
IsQueryStoredProcedure = False
BypassPrepare = True
 
I tried several SQL statements, suspecting it's a syntax issue. All of these failed, but with different error messages. These are the 2 most recent attempts based on posts I was able to locate.
SQLStatement = exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
SQLStatement = exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
 
Parameter Mapping
Variable Name = User::FileCreateDate, Direction = Input, DataType = DATE, Parameter Name = 0, Parameter Size = -1
Variable Name = User::IsNewFile, Direction = Output, DataType = BYTE, Parameter Name = 1, Parameter Size = -1
 
Result Set is empty.
Expressions is empty.
 
When I run this in debug mode with this SQL statement ...
exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the following error message appears.
 
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
 
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
 
Task failed: Compare Last File Create Date
 
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "MyPackage.dtsx" finished: Failure.

When the above is run tbl_ImportFileCreateDate does not get updated, so it's failing at some point when calling the procedure.
 
When I run this in debug mode with this SQL statement ...
exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the tbl_ImportFileCreateDate table gets updated.  So I know that data piece is working, but then it fails with the following message.
 
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.

Error: 0xC001F009 at GLImport: The type of the value being assigned to variable "User::IsNewFile" 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 Compare Last File Create Date, Execute SQL Task: Executing the query "exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "The type of the value being assigned to variable "User::IsNewFile" 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: Compare Last File Create Date

Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "MyPackage.dtsx" finished: Failure.
 
The IsNewFile global variable is scoped at the package level and has a Boolean data type, and the Output parameter in the stored procedure is defined as a Bit.  So what gives?
 
The "Possible Failure Reasons" message is so generic that it's been useless to me.  And I've been unable to find any examples online that explain how to do what I'm attempting. This would seem to be a very common task.  My suspicion is that one or more of the settings in that Execute SQL Task node is bad.  Or that there is some cryptic, undocumented reason that this is failing.
 
Thanks for your help.
 

View Replies !   View Related

---

Retrieving Result Set From Dynamically Called Stored Procedure Or Function In A Function
Is there any way I can retrieve the result set of a Stored Procedurein a function.ALTER FUNCTION dbo.fn_GroupDeviceLink(@groupID numeric)RETURNS @groupDeviceLink TABLE (GroupID numeric, DeviceID numeric)ASBEGINDeclare @command nvarchar(255)SELECT @command = Condition// @command is an SQL string or stored procedue nameFROM DeviceGroupWHERE GroupID = @groupIDINSERT @groupDeviceLinkEXEC @commandRETURNENDIs there any way i can do anything like this. @command is a variableholding the name of a stored produre. I need to run that storedprocure and return the values in such a way that they can be used in aSELECT StatementMy goal is SELECT * FROM Device INNER JOINdbo.fn_GroupDeviceLink(@groupID) ON ....this fn_GroupDeviceLink should run the proper stored procedure andreturn the values. What i also want to do is play with that result setof the specific stored procedure before i return it. Is this possible?If not, what is the work arround?ThanksMark

View Replies !   View Related

---

Using Table Name Stored In A Scalar Variable In Stored Procedure Problem
Hello to all!

I have a table name stored in a scalar variable (input parameter of my stored procedure). I need to run SQL statement: SELECT COUNT (*) FROM MyTable and store the result of my query in a scalar variable:

For example:

declare @countRows int

set @countRows = (select count(*) from MyTable)

The problem is that the name of MyTable is stored in the input variable of my stored procedure and of corse this does not work:

declare @countRows int

set @countRows = (select count(*) from @myTableName)

I also tried this:

declare @sqlQuery varchar(100)

set @sqlQuery = 'select count(*) from ' + @myTableName

set  @countRows = exec(@sqlQuery)

But it looks like function exec() does not return any value...

Any idea how to solve this problem?

Thanx,

Ziga

View Replies !   View Related

---

Stored Procedure Input Parameter (asp.net 2.0)
This should be relatively easy but for some reason it isn't. I'm trying to simply add parameters to a stored procedure that performs a simple input and I can't do it... I keep getting an error that the parameters are not found when I am explicitly stating them. I could do this with VB ASP.NET 1.x but with all these radical changes with 2.0, I'm pulling my hair out.... I can get to work if I declare a sqlStatement in the code but don't want to go that route (but will if there is no other choice) Any help would be great:
Code:
Dim cmd As New SqlDataSource
cmd.InsertCommandType = SqlDataSourceCommandType.StoredProcedure
cmd.InsertParameters.Add("@firstName", txtFirstName.Text)
cmd.InsertParameters.Add("@lastName", txtLastName.Text)
cmd.InsertParameters.Add("@address1", txtAddress1.Text)
cmd.InsertParameters.Add("@address2", txtaddress2.Text)
cmd.InsertParameters.Add("@city", txtCity.Text)
cmd.InsertParameters.Add("@state", ddlState.SelectedItem.Value)
cmd.InsertParameters.Add("@zipCode", txtZipCode.Text)
cmd.InsertParameters.Add("@telephone", txtTelephone.Text)
cmd.InsertParameters.Add("@email", txtEmail.Text)
cmd.InsertParameters.Add("@agegroup", ddlAgeGroup.SelectedItem.Value)
cmd.InsertParameters.Add("@birthday", txtBirthday.Text)
cmd.InsertParameters.Add("@emailnotification", rbEmail.SelectedItem.Value)
cmd.InsertParameters.Add("@magazine", rbEmail.SelectedItem.Value)
cmd.InsertParameters.Add("@question", txtquestion.Text)
cmd.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True"
cmd.InsertCommand = "sp_insertCustomer"

cmd.Insert()
Stored Procedure:
CREATE PROCEDURE dbo.sp_insertCustomer @firstName nchar(30),@lastName nchar(30),@address1 nchar(50),@address2 nchar(50),@city nchar(30),@state nchar(2),@zipcode nchar(10),@telephone nchar(10),@email nchar(50),@ageGroup int,@birthday dateTime,@emailNotification int,@magazine int,@question varchar(1000)

AS
INSERT tblCustomer
(firstName,lastName,address1,address2,city,state,zipCode,telephone,email,ageGroup,birthday,emailNotification,magazine,question)

Values(@firstName,@lastName,@address1,@address2,@city,@state,@zipcode,@telephone,@email,@ageGroup,@birthday,@emailNotification,@magazine,@question)
 
ERROR:
Procedure or Function 'sp_insertCustomer' expects parameter '@firstName', which was not supplied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Procedure or Function 'sp_insertCustomer' expects parameter '@firstName', which was not supplied.
Source Error:
Line 24: cmd.InsertCommand = "sp_insertCustomer"Line 25: Line 26: cmd.Insert()Line 27: Line 28:

View Replies !   View Related

---

Input Parameter To Store Procedure
Hi,
I am using a ListBox where a user can choose multiple lines.
The index of the selected items are then used in a stored procedure.

I wan´t to use the ID´s in this statement:
SELECT * FROM MyTable WHERE MyID IN (1,2,4,9)

But how can I do this?
If I pass them as a string, then I can´t use them as above.
Can I separate the string '1,2,4,9' so I can use them in the statement above?
Or can I send the values as a array to the stored procedure?


Regards!
Jonas

View Replies !   View Related

---

Parameterised Input Stored Procedure
I need to create a stored procedure with a parameterised input. An input parameter is declared as a variable, this will hold the units that a user wants to insert from TableA into TableB. Using a CASE clause, within a
stored procedure, it will first need to compare the input parameter against the avaiable units in TableA, let's say
in this case, an input of 3 units of Item_No (1235), is required to be inserted into TableB's 4 columns (refer below for field names). The stored procedure should be able to assign the units required base on the earliest Item_ID (1st Primary key comes first, on a first-in-first-out basis) from TableA, by first filtering a number of sequential criteria. First it would search for the Item_No (1235) in TableA, next its available units,
if both condition are met, next, insert a row of record into TableB's 4 columns, If units required is insufficient,
it would loop for the next available units, then inserting the next row of records into TableB. An update procedure is also required to be created to update the units being used for the assignment in TableA (refer table below
for updated records for Item_No (1235).

The following are the fieldnames in TableA running on SQL Server 2000:

Dates as datetime
Item_ID as int (Primary key)
Item_No as int
Qty as int
Unit_Price as int

TableA consist of the following info :
======================================
Dates | Item_ID | Item_No | Qty | Unit_Price
======================================
30/10/2007 | IT1000 | 1234 | 2 | 4
======================================
30/10/2007 | IT1001 | 1235 | 2 | 6
======================================
28/09/2007 | IT1002 | 1236 | 4 | 8
======================================
01/11/2007 | IT1003 | 1235 | 2 | 2

INSERT Result in TableB after executing the stored procedure :
======================================
Dates | Item_ID | Item_No | Qty | Unit_Price
======================================
30/10/2007 | IT1001 | 1235 | 2 | 6
======================================
01/11/2007 | IT1003 | 1235 | 1 | 2

UPDATE Result in TableA after executing the stored procedure :
======================================
Dates | Item_ID | Item_No | Qty | Unit_Price
======================================
30/10/2007 | IT1000 | 1234 | 2 | 4
======================================
30/10/2007 | IT1001 | 1235 | 0 | 6
======================================
28/09/2007 | IT1002 | 1236 | 4 | 8
======================================
01/11/2007 | IT1003 | 1235 | 1 | 2

The following stored procedure was created:

Create procedure dbo.up_parmins_item
@Date_DT VARCHAR(22),
@Item_No INT,
@Qty_IT INT,
@Unit_Price_IT INT as

Declare @Item_ID INT

Select @Item_ID = Item_ID
From TableA
Where Item_No = @Item_No and Qty <> 0
-- If item not exist, then insert into TableA
If @Item_ID IS NULL
BEGIN
Insert into TableA
( Dates,Item_No,Qty,Unit_Price)
Values(Getdate(),@Item_No,@Qty_IT,@Unit_Price_IT)
Set @Item_ID = @@IDENTITY
END
-- If item exist, and Qty is not 0, then insert into TableB and Update TableA
If @Item_ID IS NOT NULL and Qty <> 0
BEGIN
Set @Item_ID = Min(Item_ID),
Insert into TableB
( Dates ,Item_ID,Qty,Unit_Price)
Values(Getdate(),@Item_ID,@Qty_IT,@Unit_Price_IT)
-- update quantity balance for Item_No (1235) in TableA
Update TableA
Set Qty = (@Qty_IT - Qty)
Where Item_No = @Item_No
END

If @@Error > 0
BEGIN
RAISERROR ('Update and Insert Items failed',16,1)
RETURN 99
END

RETURN 0

My issue here is, how do I create a CASE clause or if-else statement to verify the quantity (Qty) available in TableA is sufficient for the input parameter (@Qty_IT) to be inserted into TableB? The procedure should be able to perform a loop in the CASE or If-else statement to allocate the quantity requested base on the earliest Item_ID existing in TableA, and the next available units in the subsequent Item_ID (e.g. (1st) IT1001 - 2 units, (2nd) IT1003 - balance of 1 unit) If the units requested (input parameter) for an item, does not exist in TableA, a new record is then inserted into TableA for this new item. The update statement should be able to update the
quantity for Item_No (1235) to 0 unit under Item_ID IT1001, and 1 unit under Item_ID IT1003.

Any suggestion to the above stored procedure would be helpful, Thanks guys!

View Replies !   View Related

---

Help For Stored Procedure Input Parameter
Hi All,
 
I have a project which will be a tools to edit different tables.
 
Now I need a stored_procedure to select data from different table.
 
For example I have a table name "TableFields" which have "tableID","FieldName", "DataType"and so on columns.
It has the following records.
   "1","EmployeeID","Varchar"
   "1","FirstName","varchar"
   "1","LastName","varchar"
   "1", "EmployedDate","date"
 
It has the following records.
   "2","AddressID","int"
   "2","ApartNo","varchar"
   "2", "Address","varchar"
 
 
Then I have table named "Employee" has columns "employeeID","FirstName","LastName","EmployedDate" which have the following data,
   "001","Susan","Daka","1999-09-09",
   "002","Lisa","Marzs","1999-08-08",
   "003","David","Smith","2000-01-01",
 
I also have address table has columns "AddressID","ApartNo","Address" and has the following data
   "1","1101","1208 Mornelle Crt, Toronto",
    "2","1209","1940 Garden Drive, Toronto"
  
I need to create a stored procedure to select data from table "employee " or table "address" or even other tables according to information from "TableFields."
So the table's name can be know as a input parameter, but the fields name will be a list of values and it all depends on tables.
 
I want to use fields name as a long string separated by",", like I have input "EmployeeID, FirstName,LastName" as an input parameter. But I don't know how to split the string.
 
Second, I need to create a stored procedure to insert or update data into these dynamically table.
 
Can anyone help me?
 
Thanks in advance.
 
 
 
 
 

View Replies !   View Related

---

Problem With Xml As Input To Stored Procedure
I am facing a problem while i pass xml as an intput to stored procedure.
The problem is that there are ceratin special characters which when used as a part of xml give error.Like the input which i give to my sp is :

Declare @XMLString  XML
Set @XMLString = N'<Company CompanyName = "Hilary Group & Sons"     Code = "HGS"    >
    </Company>'
Exec sproc_Insert_Company @XMLString

The error which i get on execution is: Msg 9421, Level 16, State 1, Line 2
XML parsing: line 2, character 34, illegal name character..

Its being generated because of the '&' being used in CompanyName.
 
In my sp i m using it as : .
 

Insert Into Company(

CompanyName,

Code,

 )

Output Inserted.CompanyId Into @tbl

-- TurnOver,

-- NetIncome,

-- YrOfIncorporation,

SELECT

CompanyName = ParamValues.Item.value( '@CompanyName' , 'varchar(101)'),

Code = ParamValues.Item.value( '@Code' , 'varchar(6)'),



FROM @XMLString.nodes('Company') AS ParamValues(Item)
 
Its not only this but there are other special characters which create problem like '@' and many more...

How to resolve it??
plzz do help at the earliest...
 

View Replies !   View Related

---

Stored Procedure Input Variables
Hi,

I want to convert a SQL query as shown below into a stored procedure:


select name
from namelist
where town in ('A','B','D')


If I want to make the town as the input variable into the stored procedure, how should I declare the stored procedure? As far as I know, stored procedure could only handle individual values, and not a range of values.

Thanks.

View Replies !   View Related

---

Help With XML Input Parameter For Stored Procedure
I am trying to send XML as an input parameter for a stored procedure. I have seen many articles that do a good job of describing different variations but all the examples show the stored procedure only pulling one value (field) per record from the XML input. I need to pull 3 fields for each record.

Here is an example of the XML being passed:
<object>
<property @propID="14" @propType="4" @propValue="Blah blah text" />
<property @propID="217" @propType="2" @propValue="Some other text" />
</object>

I have a table like this in my database:
CREATE TABLE SCENE_PROPERTY_LINK (ID INT, OBJ_ID INT, PROPERTY_ID INT, PROPERTY_VALUE NTEXT)
and I want a stored procedure that will accept XML and update this table.
Here is what I am trying:
CREATE PROCEDURE sp_UpdateObject
@inValues XML
AS
BEGIN
--create a temporary table
DECLARE @props TABLE(PROPID INT, PROPTYPE INT, PROPVALUE NTEXT)

--And then insert the values from the @inValues XML argument into the temporary table
--I am sure the SELECT statement is VERY wrong

INSERT INTO @props(PROPID, PROPTYPE, PROPVALUE)
SELECT @inValues('@propID', INT), @inValues('@propType', INT), @inValues('@propValue', NTEXT)
FROM @inValues.nodes('/object/property')

--...and then I will use the temp table to update the DB table (SCENE_PROPERTY_LINK) for each record where SCENE_PROPERTY_LINK.PROPERTY_ID = @props.PROPID
AND @props.PROPTYPE != 6


END

I am sure it would be more efficient to update the DB table directly from the XML argument, without using the temporary table. But, I will settle for this solution using the temp table. I have done some work creating XML output from several stored procedures but, this is the first time I have been faced with consuming XML input in SQL.

I apologize for the long post.
Thanks in advance for any help you can provide.

View Replies !   View Related

---

UpdateText Through A Stored Procedure On A Input Parameter
Hi Fellows,I am trying to write a User Defined Function in SQL 2000 to accept aParameter of type Text Field and then Do a replace function tomanipulate the some strings inside that text data. (Scenario: I havean email body and i have to replace some tags like <^ContactEmail^>and others to get the actual Data from the DB, I can do this) but myproblem I cannot do a replace function a text data type and the SQLBOL says I have to use UPDATETEXT but as I see the example I have afeeling that Updatetext can only be done on a field inside a table, Iam not sure how to get a stored procedure to accept a text data typeinput parameter and do a Updatetext on that input parameter.Your help and guide is higly appreciated.Thanks in advance.Dev

View Replies !   View Related

---