OUTPUT In SP ProductDetail For IbuySpy Store

Nov 19, 2003

In SQL Server SP theres a stored procedure called ProductDetail.

What is the OUTPUT parameter for?

What does it do?

thanks

CREATE Procedure ProductDetail

(

@ProductID int,

@ModelNumber nvarchar(50) OUTPUT,

@ModelName nvarchar(50) OUTPUT,

@ProductImage nvarchar(50) OUTPUT,

@UnitCost money OUTPUT,

@Description nvarchar(4000) OUTPUT

)

AS

SELECT

@ProductID = ProductID,

@ModelNumber = ModelNumber,

@ModelName = ModelName,

@ProductImage = ProductImage,

@UnitCost = UnitCost,

@Description = Description

FROM

Products

WHERE

ProductID = @ProductID

GO

View 11 Replies

Transact SQL :: Generic Store Procedure Call Without Output Parameters But Catching Output

Sep 21, 2015

Inside some TSQL programmable object (a SP/or a query in Management Studio)I have a parameter containing the name of a StoreProcedure+The required Argument for these SP. (for example it's between the brackets [])

EX1 : @SPToCall : [sp_ChooseTypeOfResult 'Water type']
EX2 : @SPToCall : [sp_ChooseTypeOfXMLResult 'TABLE type', 'NODE XML']
EX3 : @SPToCall : [sp_GetSomeResult]

I can't change thoses SP, (and i don't have a nice output param to cach, as i would need to change the SP Definition)All these SP 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param (it would have been so easy otherwise. So I am working on something like this but I 'can't find anything working

DECLARE @myFinalVarFilledWithCachedOutput
NVARCHAR(MAX);
DECLARE @SPToCall NVARCHAR(MAX) = N'sp_ChooseTypeOfXMLResult
''TABLE type'', ''NODE XML'';'
DECLARE @paramsDefintion = N'@CatchedOutput NVARCHAR(MAX) OUTPUT'

[code]...

View 3 Replies View Related

Store Procedure Did Not Have Output

Feb 24, 2006

naturale02 writes "Here is my SP:

CREATE procedure test as
select top 10 count (*) as Download, a.item_code as Item_Code, b.item_title as Item_Title
into report..ten
from tbl_statistics_archive a , shabox..tbl_items b
where a.item_class = '10'
and convert(char(10),a.created,111) >= convert(char(10),getdate()-7,111)
and convert(char(10),a.created,111) <= convert(char(10),getdate()-1,111)
and a.item_code = b.item_code and a.item_class = b.item_class
group by a.item_code, b.item_title
order by 1 desc
GO

I have create the table called 'ten', and i schedule this SP, but when i start the job, there was nothing shown on 'ten' table, which part i have been doing wrong, kindly help me.

Thank you"

View 1 Replies View Related

Store The Output In To A Variable

Mar 26, 2008

This is my stored procedure .It is working fine .I want to capture the output in a print statement .Can anyone help me please .

alter proc r (@id INT)

as

BEGIN

DECLARE @input VARCHAR(800)

DECLARE @c_input INT

DECLARE @i_Input INT

DECLARE @input_left VARCHAR(800)

DECLARE @delimiter CHAR(1)

select @delimiter = ','

DECLARE @in VARCHAR(800)

DECLARE @list VARCHAR(800)

declare @list2 VARCHAR(800)

SET @input = 'AWCA,GCS,IHP,Aetna'

select @c_input = (select dbo.Fx_CharCount(@delimiter,@input))

set @c_input = @c_input + 1

while @c_input > 0

BEGIN

select @i_input = charindex(@Delimiter,@input)

if @i_input != 0

BEGIN

select @input_left = left(@input, @i_input - 1)

END

else

select @input_left = @input

select @in = '''' + @input_left + ''''

select @list = ISNULL(@list + ',', '') + @in

select @input = right(@input ,(len(@input) - @i_input))

SET @c_input = @c_input -1

if @c_input = 0 or @input = @input_left

break

end

Print @list

EXECUTE ('SELECT Label FROM repricingsystemtype WHERE Label Not IN (' + @list + ')')

END

View 5 Replies View Related

Store Xml Output In Text Format

Jun 4, 2008

I have the following Query which gives the output in XML format.
select * from <Table_Name> For XML auto
I wanted to store this output in a .Xml file at my local machine.
I would prefer to have a proc when called generates the xml and stores it on the local m/c. Any ideas ?
Thanks,

View 3 Replies View Related

Retrieve Output Value From Store Procedure

Jan 24, 2006

I'm trying to use a store procedure to add an item into the database and retrieve the id of such item within and output parameter but this is not happening.
The item is added into the db but the output param is not modified.
Here is the code:
SP
CREATE procedure dbo.AddItem(@Desc nvarchar(100),@intItemID int output)as insert into RR_Item ( desc ) values ( @Desc )
select @intItemID = SCOPE_IDENTITY()GO
I have tried in the last line of the SP
select @intItemID = SCOPE_IDENTITY()
select @intItemID = @@IDENTITY
select @intItemID = max(itemid) from RR_Item
but nothing seems to work.
I'm calling the store procedure as follows from asp.net:
Dim intItemID As New SqlParameter("@intItemID", SqlDbType.Int)
intItemID.Direction = ParameterDirection.Output
SqlHelper.ExecuteDataset(objConn.ConnectionString, "AddItem", desc, intItemID)
MsgBox(intItemID.Value.ToString)

Any help would be appreciated.
Thanks

View 1 Replies View Related

Capturing The Output From Store Procedure And Use It

Jan 23, 2004

How do I call capture the output (not return value) from calling a store procedure from within a store procedure so I can use that data for further processing (say join it with another table)?

For example,

CREATE PROCEDURE dbo.sp_test AS
--- returns all words not in Mastery Level 0

EXEC sp_anothertest

--- use the data coming back from sp_test and join it with another table here and say insert them into tblFinalResults

SELECT * tblFinalResults
GO

Thanks!

View 1 Replies View Related

How To Get Store Procedure's Return Value And Output Parameters From BLL?

Oct 10, 2007

ex:
myprocedure(@Cusname varchar(50), @Cusid int output)as
Insert into Customer(Cusname) values (@Cusname)SELECT @cusid = @@IDENTITY
i add the query to my adapter called CreatCustomer (@Cusnam,@Cusid)private Merp_CusListTableAdapter _CuslistAdapter = null;protected Merp_CusListTableAdapter Adapter
{
get
{if (_CuslistAdapter == null)
_CuslistAdapter = new Merp_CusListTableAdapter();return _CuslistAdapter;
}
}
Now how i write function in BLL to receive output paramter from creatcustomer function?

View 1 Replies View Related

Creating A Store Procedure With Output Parameter

Oct 22, 2005

Hi all,Could someone give me an example on how to create and execute the store procedure with using output parameter?In normal, I create the store procedure without using output parameter, and I did it as follow:CREATE PROC NewEmployee (ID int(9), Name Varchar (30), hiredate DateTime, etc...)ASBEGIN //my codeENDGOWhen i executed it, I would said: Execute NewEmployee 123456789, 'peter mailler', getDate(), etc....For output parameter:CREATE PROC NewEmployee (ID int(9), Name Varchar (30), hiredate DateTime, @message Varchar(40) out)ASBEGIN insert into Employee ...... //if error encountered set @message = "Insertion failure"ENDGOExec NewEmployee 123456789, 'peter mailler', getDate(), do I need to input something for the output parameter here?Anyone could give me an example on how to handle the output parameter within the store procedure coz I am not sure how to handle it?Many thanks.

View 1 Replies View Related

Store Webservice Task Output To File

Feb 14, 2007

Hi

I have designed a package that reads in rows from an Excel file into a recordset then loops through the recordset sending two parameters to a webservice task. This works fine however I now need to output the results of the webservice task to a file or recordset - I have tried outputting to a file however it only stores the last result as the file is overwritten each time - I am new to SSIS and am sure there must be a really simple way to do this but cannot find an help on how to do it !

Thanks in advance K

View 4 Replies View Related

I Can Return INT From A Store Procedure But Get Error When OUTPUT A Varchar

Mar 21, 2008

My store procedure get the QuestionID (PK) from the page and then it's to return a few varchars but gives me the error that string can't be converted to int. ALTER PROCEDURE [dbo].[usp_getQuestionsforEditPopulateText]@QuestionID int,@QuestionDescription varchar(MAX) OUTPUT,@Option1 varchar(50) OUTPUT,@Option2 varchar(50) OUTPUT,@Option3 varchar(50) OUTPUT,@Option4 varchar(50) OUTPUT,@Option5 varchar(50) OUTPUT,@reference varchar(50) OUTPUT,@chb1 int OUTPUT,@chb2 int OUTPUT,@chb3 int OUTPUT,@chb4 int OUTPUT,@chb5 int OUTPUTAsSet @QuestionDescription =(Select questionDescription from QuestionsBank Where questionID = @QuestionID)Set @Option1 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 1)Set @Option2 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 2)Set @Option3 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 3)Set @Option4 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 4)Set @Option5 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 5)Set @reference = (Select referencedescription from reference where questionID = @QuestionID)Set @chb1 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 1)Set @chb2 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 2)Set @chb3 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 3)Set @chb4 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 4)Set @chb5 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 5) RETURN This is what the page callsDim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection("myconnectionstring ") Dim cmdUpdate As New Data.SqlClient.SqlCommand("usp_getQuestionsforEditPopulateText", dbConnection) cmdUpdate.CommandType = Data.CommandType.StoredProcedure cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@QuestionID", Data.SqlDbType.Int)) cmdUpdate.Parameters("@QuestionID").Value = QuestionID cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@QuestionDescription", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@QuestionDescription").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option1", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option1").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option2", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option2").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option3", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option3").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option4", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option4").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option5", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option5").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@reference", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@reference").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb1", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb1").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb2", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb2").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb3", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb3").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb4", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb4").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb5", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb5").Direction = Data.ParameterDirection.Output 'open connection dbConnection.Open() 'Execute non query cmdUpdate.ExecuteNonQuery() 'close connection dbConnection.Close()

View 8 Replies View Related

SQL 2012 :: Store Procedure Only Output One Select Statement

May 28, 2014

There are about 10 select statements in a store procedure.

All select statements are need.

Is it possible to output only the result of last select statement?

View 2 Replies View Related

Store Proc - How To Get Patient Status As Output To Form

Dec 15, 2013

I am using below code to get patient status as an out put to my form. not sure whats happening but each time I run this its not executing my last "IF" if set to "N" show me N if not show me "Y" but it is by passing my first "IF" condition and jumps to last?

The column alerts_ind shows only Y or N in the table patient_status.

The table patient_status_mstr show the description of the patient which "discharged". All I want to do is if the patient is flagged with "discharge" the columns "alerts_ind" shows "Y". but something wrong? below is the code.

Alter PROCEDURE GBCheckPatientStatus (@enc_id varchar(36), @data_ind Char(1) OUTPUT)

as
begin
declare
@alerts_ind char(1);
select @alerts_ind =pm.alerts_ind

[Code] ....

View 5 Replies View Related

How To Store The Output Of Stored Procedure To A Temp Table

Jan 28, 2008

Hi all,

I've a requirement to store the output of the stored procedure into temp. tables/ table varibles.
I've 4 select statements as my output of the stored procedure. How do I store the results of all the 4 select stmnts into 4 different temp tables.

Simplified SP is as...

Create procedure usp_test
as
begin

select c1,c2 from table1
select c3,4 from table2
select c9,c8 from table3
select c5,c7 from Table4
end

I'm expecting something like this...

declare @table1 table (c1, c2)
insert into @table1
Exec <Sp_Name>

select * from @table1

I know the above stmnt works, if my SP has only 1 select stmnt as output.
Please help me to acheive this for multiple select statements.

Thanks,

View 5 Replies View Related

Store The Output Of Sp_executesql - Solved With Managed Code

Apr 18, 2006

hi

I am trying to store the output of sp-executesql into a variable to implement it as a user defined function later

The function is

ALTER function [dbo].[UnitsAvailable] (@id int)

returns int

as

begin

declare @sql nvarchar(100)

declare @params nvarchar(500)

declare @count nvarchar(10)

set @sql = N'Select count(*) from units where projectid=' + convert(varchar,@id) + 'and sold=0 and displayunit=1'

set @params = N'@countOUT nvarchar(10) OUTPUT';

exec sp_executesql @sql, @params, @countOUT=@count OUTPUT;

return @count

end

The result is that I am able to parameterize the sql end execute with the right result. The only problem is that the value is not stored in the variable @count. I could get to the same result using managed code in sql 2005 but still I am curious to find out where the problem is ....

Can you please help?

Thanks Alex

View 6 Replies View Related

Number Of ROWS Of Output Of Aggregate Transformation Sometimes Doesn't Match The Output From T-SQL Query

Dec 25, 2006

While using Aggregate Transformation to group one column,the rows of output sometimes larger than the rows returned by a T-SQL statement via SSMS.

For example,the output of the Aggregate Transformation may be 960216 ,but the

'Select Count(Orderid) From ... Group By ***' T-SQL Statement returns 96018*.

I'm sure the Group By of the Aggregate Transformation is right!

But ,when I set the "keyscale" property of the transformation,the results match!

In my opinion,the "keyscale" property will jsut affects the performance of the transformaiton,but not the result of the transformation.

Thanks for your advice.

View 2 Replies View Related

Output And Error Output Write The Same Table At The Same Time, Stall The Process.

Aug 30, 2006

Hi

I have Lookup task to determine if source data should be updated to or insert to the customer table. After Lookup task, the Error Output pipeline will redirect to insert new data to the table and the Output pipeline will update customer table. But these two tasks will be processing at the same time which causes stall on the process. Never end.....

The job is similiart to what Slow Changing Dimention does but it won't update the table at the same time.

What can I do to avoid such situation?

Thanks in advance,

JD

View 4 Replies View Related

Using Output From A Stored Procedure As An Output Column In The OLE DB Command Transformation

Dec 8, 2006

I am working on an OLAP modeled database.

I have a Lookup Transformation that matches the natural key of a dimension member and returns the dimension key for that member (surrogate key pipeline stuff).

I am using an OLE DB Command as the Error flow of the Lookup Transformation to insert an "Inferred Member" (new row) into a dimension table if the Lookup fails.

The OLE DB Command calls a stored procedure (dbo.InsertNewDimensionMember) that inserts the new member and returns the key of the new member (using scope_identity) as an output.

What is the syntax in the SQL Command line of the OLE DB Command Transformation to set the output of the stored procedure as an Output Column?

I know that I can 1) add a second Lookup with "Enable memory restriction" on (no caching) in the Success data flow after the OLE DB Command, 2) find the newly inserted member, and 3) Union both Lookup results together, but this is a large dimension table (several million rows) and searching for the newly inserted dimension member seems excessive, especially since I have the ID I want returned as output from the stored procedure that inserted it.

Thanks in advance for any assistance you can provide.

View 9 Replies View Related

Query Produces Jumbled Output / Output Not In Sequence

Jul 23, 2005

Hi!Server info -Win2K3 Server +SP1 with 1 GB Memory and 1.5 GB Virtual MemorySQL Server 2000 Enterprise Edition + SP3 running on this.Required result -Create a SQL Script that will contain a set of create, update, insert& delete statements (about 17500 lines including blank lines) thatcan be run on different databases seperatelyHow we do this -We have a SP - that creates a temporary table and then calls anotherSP that actually populates the temporary table created by the first SP*Samples of both SPs are below -PROBLEMThe result is directed to a file -However when the query is run it runs through the entire script but'Jumbles' the outputRunning the same scripts on a copy of the database on other machineswork fine and the size of the outfiles is exactly the sameI have increased the page size to 2.5 GB and restarted the server.Running the sp now generated the correct output a few times but gotjumbled as before after a few more users logged in and activity on theserver increased.Another interesting point is that the output is jumbled exactly thesame way each time. It seems the sql executes correctly and writesthe output in chunks only writting the chunks out of sequence - butin the same sequence each time.e.g. of expected resultInsert into Table1Values x, y, z, 1, 2Insert into Table1Values q, s, g, 3, 4Insert into Table1Values c, d, e, 21, 12....Insert into Table2Values ...Insert into Table3Values ................Update RefGenSet Last = 1234Where RefGenRef = 1JUMBLED OUTPUTInsert into Table1Values x, y, z, 1, 2Insert into Table1Values q, s, g, 3, 4Insert into Table1Values c, d, e, 21, 12....Insert into Table2Values ...Insert into Table2Values ...Values ...Update RefGenSet Last = 1234Where RefGenRef = 1Insert into Table3Values ................Insert into Table1Values c, d, e, 21, 12....Insert into Table2----------------------------------------Sample of First Script - STATDATA_RSLT**************************************SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOSET NOCOUNT ONGOCREATE PROCEDURE StatData_rsltASCREATE TABLE #tbl_Script(ScriptText varchar(4000))EXEC TestStatData_intSELECT t.ScriptTextFROM #tbl_Script tGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO*******************************************Sample of CALLED SP - TestStatData_int*******************************************SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOCREATE PROCEDURE TestStatData_intASDECLARE @AttrRef int,@TestID int,@PrtTestRef int,@AttrType tinyint,@EdtblSw tinyint,@NmValRef int,@SrtTypeRef int,@AttrStr varchar(20),@TestStr varchar(20),@PrtTestStr varchar(20),@AttrTypeStr varchar(20),@EdtblStr varchar(20),@NmValStr varchar(20),@SrtTypeStr varchar(20),@TestRef int,@Seq int,@PrtRef int,@Value varchar(255),@TermDate datetime,@AttrID int,@DefSw tinyint,@WantSw tinyint,@TestRefStr varchar(20),@SeqStr varchar(20),@PrtStr varchar(20),@TermDateStr varchar(255),@AttrIDStr varchar(20),@DefStr varchar(20),@WantStr varchar(20),@LanRef int,@LanStr varchar(20),@Code varchar(20),@Desc varchar(255),@MultiCode varchar(20),@MultiDesc varchar(255),@InhSw tinyint,@InhStr varchar(20),@InhFrom int,@InhFromStr varchar(20),@Lan_TestRef int,@ActSw tinyint,@ActSwStr varchar(20)SELECT @Lan_TestRef = dbo.fn_GetTestRef('Lan')INSERT INTO #tbl_ScriptVALUES('')-- Create tablesINSERT INTO #tbl_ScriptVALUES ('CREATE TABLE #tbl_Test (AttrRef int, TestID int , PrtTestRefint, AttrType tinyint, EdtblSw tinyint, NmValRef int, SrtTypeRefint)')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES('CREATE TABLE #tbl_TestAttr(AttrRef int, TestRef int, Seq int,PrtRef int, AttrType tinyint, Value varchar(255), TermDate datetime,AttrID int, DefSw tinyint, WantSw tinyint, ActSw tinyint)')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('CREATE TABLE #tbl_AttrName(AttrRef int, LanRef int, Codevarchar(20), [Desc] varchar(255), MultiCode varchar(20), MultiDescvarchar(255), InhSw tinyint, InhFrom int)')INSERT INTO #tbl_ScriptVALUES ('')-- insert Test valuesDECLARE Test_cursor CURSOR FORSELECT l.AttrRef, l.TestID, l.PrtTestRef, l.AttrType, l.EdtblSw,l.NmValRef, l.SrtTypeRefFROM Test lOPEN Test_cursorFETCH NEXT FROM Test_cursorINTO @AttrRef, @TestID, @PrtTestRef, @AttrType, @EdtblSw, @NmValRef,@SrtTypeRefWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@TestStr = ISNULL(CAST(@TestID as varchar), 'NULL'),@PrtTestStr = ISNULL(CAST(@PrtTestRef as varchar), 'NULL'),@AttrTypeStr = ISNULL(CAST(@AttrType as varchar), 'NULL'),@EdtblStr = ISNULL(CAST(@EdtblSw as varchar), 'NULL'),@NmValStr = ISNULL(CAST(@NmValRef as varchar), 'NULL'),@SrtTypeStr = ISNULL(CAST(@SrtTypeRef as varchar), 'NULL')INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_Test(AttrRef, TestID, PrtTestRef,AttrType,EdtblSw, NmValRef, SrtTypeRef)')INSERT INTO #tbl_ScriptVALUES ('VALUES ( ' + @AttrStr + ', ' + @TestStr + ', ' +@PrtTestStr+ ', ' + @AttrTypeStr + ', ' + @EdtblStr + ', ' + @NmValStr + ', ' +@SrtTypeStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM Test_cursorINTO @AttrRef, @TestID, @PrtTestRef, @AttrType, @EdtblSw, @NmValRef,@SrtTypeRefENDCLOSE Test_cursorDEALLOCATE Test_cursorDECLARE TestAttr_cursor CURSOR FORSELECT le.AttrRef, le.TestRef, le.Seq, le.PrtRef, le.AttrType,le.Value,le.TermDate, le.AttrID, le.DefSw, le.WantSw, le.ActSwFROM TestAttr leWHERE le.WantSw = 1AND le.ActSw = 1OPEN TestAttr_cursorFETCH NEXT FROM TestAttr_cursorINTO @AttrRef, @TestRef, @Seq, @PrtRef, @AttrType, @Value,@TermDate, @AttrID, @DefSw, @WantSw, @ActSwWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@TestRefStr = ISNULL(CAST(@TestRef as varchar), 'NULL'),@SeqStr = ISNULL(CAST(@Seq as varchar), 'NULL'),@PrtStr = ISNULL(CAST(@PrtRef as varchar), 'NULL'),@AttrTypeStr = ISNULL(CAST(@AttrType as varchar), 'NULL'),@Value = ISNULL(@Value, 'NULL'),@TermDateStr = ISNULL(CAST(@TermDate as varchar), 'NULL'),@AttrIDStr = ISNULL(CAST(@AttrID as varchar), 'NULL'),@DefStr = ISNULL(CAST(@DefSw as varchar), 'NULL'),@WantStr = ISNULL(CAST(@WantSw as varchar), 'NULL'),@ActSwStr = ISNULL(CAST(@ActSw as varchar), '1')SELECT @Value = '''' + @Value + ''''WHERE @Value <> 'NULL'INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_TestAttr(AttrRef, TestRef, Seq, PrtRef,AttrType, Value, TermDate, AttrID, DefSw, WantSw, ActSw)')INSERT INTO #tbl_ScriptVALUES ('VALUES (' + @AttrStr + ', ' + @TestRefStr + ', ' +@SeqStr+ ', ' + @PrtStr + ', ' + @AttrTypeStr + ', ' + @Value + ', ' +@TermDateStr + ', ' + @AttrIDStr + ', ' + @DefStr + ', ' + @WantStr+', '+ @ActSwStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM TestAttr_cursorINTO @AttrRef, @TestRef, @Seq, @PrtRef, @AttrType, @Value,@TermDate, @AttrID, @DefSw, @WantSw, @ActSwENDCLOSE TestAttr_cursorDEALLOCATE TestAttr_cursorDECLARE AttrName_cursor CURSOR FORSELECT e.AttrRef, e.LanRef, e.Code, e.[Desc], e.MultiCode,e.MultiDesc, e.InhSw, e.InhFromFROM AttrName e, TestAttr leWHERE e.LanRef = 0AND e.AttrRef = le.AttrRefAND le.WantSw = 1AND le.ActSw = 1OPEN AttrName_cursorFETCH NEXT FROM AttrName_cursorINTO @AttrRef, @LanRef, @Code, @Desc, @MultiCode,@MultiDesc, @InhSw, @InhFromWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@LanStr = ISNULL(CAST(@LanRef as varchar), 'NULL'),@Code = ISNULL(@Code, 'NULL'),@Desc = ISNULL(@Desc, 'NULL'),@MultiCode = ISNULL(@MultiCode, 'NULL'),@MultiDesc = ISNULL(@MultiDesc, 'NULL'),@InhStr = ISNULL(CAST(@InhSw as varchar), 'NULL'),@InhFromStr = ISNULL(CAST(@InhFrom as varchar), 'NULL')SELECT @Code = REPLACE(@Code, '''',''''''),@Desc = REPLACE(@Desc, '''','''''') ,@MultiCode = REPLACE(@MultiCode, '''','''''') ,@MultiDesc = REPLACE(@MultiDesc, '''','''''')INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_AttrName(AttrRef, LanRef, Code, [Desc],MultiCode, MultiDesc, InhSw, InhFrom)')INSERT INTO #tbl_ScriptVALUES ('VALUES (' + @AttrStr + ', ' + @LanStr + ', ''' + @Code +''', ''' + @Desc + ''', ''' + @MultiCode + ''', ''' + @MultiDesc +''',' + @InhStr + ', ' + @InhFromStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM AttrName_cursorINTO @AttrRef, @LanRef, @Code, @Desc, @MultiCode,@MultiDesc, @InhSw, @InhFromENDCLOSE AttrName_cursorDEALLOCATE AttrName_cursor-- Do update TestAttr dataINSERT INTO #tbl_ScriptVALUES ('UPDATE le')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' le.TestRef = t.TestRef,')INSERT INTO #tbl_ScriptVALUES (' le.PrtRef = t.PrtRef,')INSERT INTO #tbl_ScriptVALUES (' le.AttrType = t.AttrType,')INSERT INTO #tbl_ScriptVALUES (' le.Value = t.Value,')INSERT INTO #tbl_ScriptVALUES (' le.TermDate = t.TermDate,')INSERT INTO #tbl_ScriptVALUES (' le.AttrID = t.AttrID,')INSERT INTO #tbl_ScriptVALUES (' le.DefSw = t.DefSw,')INSERT INTO #tbl_ScriptVALUES (' le.WantSw = t.WantSw,')INSERT INTO #tbl_ScriptVALUES (' le.ActSw = t.ActSw')INSERT INTO #tbl_ScriptVALUES ('FROM TestAttr le, #tbl_TestAttr t')INSERT INTO #tbl_ScriptVALUES ('WHERE le.AttrRef = t.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')-- Update AttrNameINSERT INTO #tbl_ScriptVALUES ('UPDATE en')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' en.Code = te.Code,')INSERT INTO #tbl_ScriptVALUES (' en.[Desc] = te.[Desc],')INSERT INTO #tbl_ScriptVALUES (' en.MultiCode = te.MultiCode,')INSERT INTO #tbl_ScriptVALUES (' en.MultiDesc = te.MultiDesc,')INSERT INTO #tbl_ScriptVALUES (' en.InhSw = te.InhSw,')INSERT INTO #tbl_ScriptVALUES (' en.InhFrom = te.InhFrom')INSERT INTO #tbl_ScriptVALUES ('FROM AttrName en, #tbl_AttrName te')INSERT INTO #tbl_ScriptVALUES ('WHERE en.AttrRef = te.AttrRef')INSERT INTO #tbl_ScriptVALUES (' AND en.LanRef = te.LanRef')INSERT INTO #tbl_ScriptVALUES (' AND te.LanRef = 0')-- Do update Test the dataINSERT INTO #tbl_ScriptVALUES ('UPDATE l')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' l.TestID = t.TestID,')INSERT INTO #tbl_ScriptVALUES (' l.PrtTestRef = t.PrtTestRef,')INSERT INTO #tbl_ScriptVALUES (' l.AttrType = t.AttrType,')INSERT INTO #tbl_ScriptVALUES (' l.EdtblSw = t.EdtblSw,')INSERT INTO #tbl_ScriptVALUES (' l.NmValRef = t.NmValRef')INSERT INTO #tbl_ScriptVALUES ('FROM Test l, #tbl_Test t')INSERT INTO #tbl_ScriptVALUES ('WHERE l.AttrRef = t.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')--DELETE where just updatedINSERT INTO #tbl_ScriptVALUES ('DELETE FROM t')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_Test t, Test l')INSERT INTO #tbl_ScriptVALUES ('WHERE t.AttrRef = l.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DELETE FROM t')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_TestAttr t, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE t.AttrRef = le.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DELETE FROM te')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_AttrName te, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE te.AttrRef = le.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')-- Insert TestAttrINSERT INTO #tbl_ScriptVALUES ('INSERT INTO TestAttr (AttrRef, TestRef, Seq, PrtRef,AttrType,Value, TermDate, AttrID, DefSw, WantSw, ActSw)')INSERT INTO #tbl_ScriptVALUES ('SELECT t.AttrRef, t.TestRef, t.Seq, t.PrtRef, t.AttrType,t.Value, t.TermDate, t.AttrID, t.DefSw, t.WantSw, t.ActSw')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_TestAttr t')INSERT INTO #tbl_ScriptVALUES ('')-- AttrNameINSERT INTO #tbl_ScriptVALUES ('INSERT INTO AttrName(AttrRef, LanRef, Code, [Desc],MultiCode,MultiDesc, InhSw, InhFrom)')INSERT INTO #tbl_ScriptVALUES ('SELECT te.AttrRef, le.AttrRef, te.Code, te.[Desc],te.MultiCode, te.MultiDesc, ')INSERT INTO #tbl_ScriptVALUES (' CASE le.AttrRef ')INSERT INTO #tbl_ScriptVALUES (' WHEN 0 THEN 0')INSERT INTO #tbl_ScriptVALUES (' ELSE 1 END,')INSERT INTO #tbl_ScriptVALUES (' CASE le.AttrRef ')INSERT INTO #tbl_ScriptVALUES (' WHEN 0 THEN NULL')INSERT INTO #tbl_ScriptVALUES (' ELSE 0 END')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_AttrName te, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE le.TestRef = ' + CAST(@Lan_TestRef as varchar))INSERT INTO #tbl_ScriptVALUES ('')-- Insert new rowsINSERT INTO #tbl_ScriptVALUES ('INSERT INTO Test(AttrRef, TestID, PrtTestRef, AttrType,EdtblSw, NmValRef, SrtTypeRef)')INSERT INTO #tbl_ScriptVALUES ('SELECT t.AttrRef, t.TestID, t.PrtTestRef, t.AttrType,t.EdtblSw, t.NmValRef, t.SrtTypeRef')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_Test t')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_Test')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_TestAttr')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_AttrName')-- Update RefGenDECLARE @RefGenReflast int,@RefGenRefStr varchar(10)SELECT @RefGenReflast = lastFROM RefGenWHERE RefGenRef = 1SELECT @RefGenRefStr = ISNULL(CAST(@RefGenReflast as varchar), 'NULL')INSERT INTO #tbl_ScriptVALUES('')INSERT INTO #tbl_ScriptVALUES('UPDATE RefGen')INSERT INTO #tbl_ScriptVALUES ('SET Last = ' + @RefGenRefStr)INSERT INTO #tbl_ScriptVALUES ('WHERE RefGenRef = 1')INSERT INTO #tbl_ScriptVALUES ('')GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO*******************************RegardsGlenn

View 5 Replies View Related

PrimeOutput : Difference Between 'Output' And 'output Buffer'

Aug 12, 2005

When overriding the PrimeOutput method in a custom component, you get as parameters the outputIDs and the output buffers (of type PipelineBuffer). using the outputIDs you can get IDTSOutput90 outputs.

View 5 Replies View Related

Store Procedure Not Store

Nov 20, 2013

My store Procedure is not save in Strore Procedure folder at the time of saving it give me option to save in Project folder and file name default is SQLQuery6.sql when i save it after saving when i run my store procedure

exec [dbo].[SP_GetOrdersForCustomer] 'ALFKI'

I am getting below error :

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.SP_GetOrdersForCustomer'.

View 13 Replies View Related

Call Store Procedure From Another Store Procedure

Nov 13, 2006

I know I can call store procedure from store procedure but i want to take the value that the store procedure returned and to use it:

I want to create a table and to insert the result of the store procedure to it.

This is the code: Pay attention to the underlined sentence!

ALTER PROCEDURE [dbo].[test]

AS

BEGIN

SET NOCOUNT ON;

DROP TABLE tbl1

CREATE TABLE tbl1 (first_name int ,last_name nvarchar(10) )

INSERT INTO tbl1 (first_name,last_name)

VALUES (exec total_cash '8/12/2006 12:00:00 AM' '8/12/2006 12:00:00 AM' 'gilad' ,'cohen')

END

PLEASE HELP!!!! and God will repay you in kind!

Thanks!

View 7 Replies View Related

To Store Or Not To Store In The Db

Jul 31, 2006

Hi allI am using .jpg images in my application, I am currently storing all my images in a file location, and placing their file path in the database. The problem with this is that I cannot display the images in crystal reports (version with vs.net 2005). I can however display the images if I store them as bytes in an image data type in my db.So the question is should I store either the file path of the bytes of the image in the database table? What are the contraints, I know that size is one, but will speed & performance be affected?Mike55.

View 2 Replies View Related

Store Dd-MMM Only

Sep 6, 2007

I want to store only dd-MMM part of date in a DateTime field. Is it possible? If I take a varchar field, how to query while converting it to date.

View 4 Replies View Related

Help With Store Function.

Jul 6, 2006

Hi everybody.

I am using SQL Server 2000.

My users table looks lie this:
userID (primary key)
firstName
lastName
userName
userCode
Industry
Company
Office
dateCreated
Password
…

Every time that I create a new user I have to create userCode (for internal use).
userCode should be created from first letter of the FirstName, first letter from LastName and a number.
Example:
firstName:         lastName:         userCode:
Joe                   Blow                jb1
Jake                 Brinks              jb2
Ann                  Smith                as1
…

If same userCode already exist the in the same Industry and in the same Company and in the same Office then createCode function should increment the number (the third character of the userCode) up to 9

I never did this before.
Any help would be highly appreciated.
Alex.

View 12 Replies View Related

SQL Store Proc - Need Help

May 4, 2007

I need some help writing my request, I tried both of the following but it does'st seem to work. Your sugestion would be appreciated.SELECT count(LeadId) FROM dbo.Cl_Leads Where AccntMng=@AccntMng and (Status = 'Won' or Status = 'Lost') and InsertDate BETWEEN @dtStart AND @dtEnd
I also tried
SELECT count(LeadId) FROM (SELECT * FROM dbo.Cl_Leads WHERE InsertDate BETWEEN @dtStart AND @dtEnd)Where AccntMng=@AccntMng and (Status = 'Won' or Status = 'Lost') This return and error: Incorrect syntax near the keyword 'Where'.

View 6 Replies View Related

Store Procedure

Feb 17, 2008

Hi guysi would really appreciate your help here. what am i doing wrong with this stored procedure ALTER PROCEDURE usp_CreateNewCustomer( @LName as varchar(50), @FName as varchar(50), @Dept as varchar(50)=NULL, @PhoneType as varchar(50)=NULL, @Complete as Bit=NULL, @CustomerID as Int OUTPUT, @FaxModel as varchar(50)=NULL, @FaxNumber as varchar(50)=NULL, )ASINSERT INTO [CustomerInfo] ([LName], [FName], [Dept], [PhoneType], [Complete]) VALUES (@LName, @FName, @Dept, @PhoneType, @Complete)SELECT SCOPE_IDENTITY()INSERT INTO Extras (CustomerID, FaxModel, FaxNumber) VALUES (@CustomerID, @FaxModel, @FaxNumber)RETURN It keeps on complaning "'usp_CreateNewCustomer' expects parameter '@CustomerID', which was not supplied."thanks

View 4 Replies View Related

Store Procedure?

Feb 28, 2008

Hi all,
I have a few question regarding SPROC. Firstly, I want to create a sp, that will return a multiple column of data, how do you achieve that. Secondly, I want to create a store procedure that will return a multiple columns in a cursor as an output variable. Any help will be much appreciated. Can you have more then 1 return parameters???
Thanks.
Kabir

View 2 Replies View Related

Where Do You Store Your SQL Strings

Feb 28, 2008

In an ASP.NET app, where is the preferred place (or your preferred place, I should say) to store any SQL Statements (updates, selects, stored procedure calls, etc.) you have? I have been using a key/value pair in AppSettings in my web.config but just feel like that isn't the best way. What say you?

View 4 Replies View Related

How To Run SQL Store Procedure Using Asp.net

Mar 5, 2008

I have asp.net web application which interface with SQL server,
I want to run store procedure query of SQL using my asp.net application.
How to declare connectons strings, dataset, adapter etc to run my store procedure resides in sql server.
for Instance Dim connections as string,
Dim da as dataset, Dim adpt as dataadapter. etc
if possible , then show me completely code so I can run store procedure using my button click event in my asp.net application
thank you
maxmax

View 2 Replies View Related

Store Procedures

Jan 27, 2004

hi everyone :)

I've got a store procedure(sp) on the database side ( MS SQL 2000 )

and want to access the sp via ASP.net

so simply I have got few textboxs, which I am getting the values from and I need to pass this data to my sp and get an output of a string from my sp.

I've tested the SP and it does work fine

can you just give me a template of how to access the sp from ASP.net

mant thanks indeed

M

View 2 Replies View Related

Question About Best Way To Store An Up Or Down Value

Feb 11, 2004

I'm creating a table for maintenance records.

In each record many of the values are simply checkboxes.

In the database for these attributes, is a good way to store the state of these checkboxes as simple as 0 for false, 1 for true?

-David

View 1 Replies View Related

Store Files In DB

Apr 26, 2004

Hi All,
I want to store files (any kind of file) in DB and retrieve them.What is the best method to do it and also please post sample code as I have never done that.
Thanks,
Kumar.

View 1 Replies View Related