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
ADVERTISEMENT
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
Apr 11, 2008
Is possible to get the name of the store procedure that i'm executing ?
I need to get the name of the procedure inside the procedure being executed, to insert this value on a log file...
View 1 Replies
View Related
May 18, 2007
Hi,
I'm having problems retrieving the output parameter from my stored procedure, i'm getting the following error
An SqlParameter with ParameterName '@slideshowid' is not contained by this SqlParameterCollection code as follows:
int publicationId = (int) Session["PublicationId"];string title = txtTitle.Text;int categoryid = Convert.ToInt32(dlCategory.SelectedItem.Value);SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);SqlCommand myCommand = new SqlCommand("sp_be_addSlideshow", myConnection);myCommand.CommandType = CommandType.StoredProcedure;myCommand.Parameters.Add(new SqlParameter("@publicationid", SqlDbType.Int));myCommand.Parameters["@publicationid"].Value = publicationId;myCommand.Parameters.Add(new SqlParameter("@title", SqlDbType.NVarChar));myCommand.Parameters["@title"].Value = title;myCommand.Parameters.Add(new SqlParameter("@categoryid", SqlDbType.Int));myCommand.Parameters["@categoryid"].Value = categoryid;myConnection.Open();myCommand.ExecuteNonQuery();string slideshowId = myCommand.Parameters["@slideshowid"].Value.ToString();myConnection.Close();
my stored procedure:
CREATE PROCEDURE sp_be_addSlideshow( @publicationid int, @title nvarchar(50), @categoryid int, @slideshowid int = NULL OUTPUT)AS
INSERT INTO Slideshow(publicationid,title,slideshowcategoryid,deleted)
VALUES (@publicationid,@title,@categoryid,0)
SELECT @slideshowid = SCOPE_IDENTITY()GO
View 1 Replies
View Related
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
Jul 20, 2005
in my java application I've made a call to this stored procedureCREATE procedure pruebaICM@pANI varchar(20),@pTABLA varchar(20),@pInsert varchar(500),@pUpdate varchar(1000),@pFLAG varchar(1),@pResultado int OUTPUTasbeginDECLARE @ani varchar(20)declare @cliente intDECLARE @sentencia nvarchar(1000)DECLARE @tabla nvarchar(20)DECLARE @sentencia_where nvarchar(50)DECLARE @sql nvarchar(1050)SET NOCOUNT ONset @tabla = @pTABLAset @ani = @pANISELECT @sql= N'select @cliente=count(ani) from '+ @tabla + N' whereani = ' + @aniexec sp_executesql @sql, N'@Cliente INT OUTPUT', @Cliente OUTPUTSELECT @Clienteif (@pFLAG = 'A') or (@pFLAG = 'Actualizar') or (@pFLAG = 'I')beginif (@cliente = 0)beginset @sentencia = N'insert into ' +@pTABLA + N' values (' + @pInsert + N')'EXEC sp_executesql @sentenciaset @pResultado = 1SELECT @pResultadoreturn @pResultadoendif (@cliente = 1)beginset @sentencia = N'update ' + @pTABLA +N' set ' + @pUpdateset @sentencia_where = N' where ANI =' + @pANIset @sql = @sentencia +@sentencia_whereEXEC sp_executesql @sqlset @pResultado = 2SELECT @pResultadoreturn @pResultadoendendelse if (@pFLAG = 'B') or (@pFLAG = 'Borrar')beginif (@cliente = 0)beginset @pResultado = 0SELECT @pResultadoreturn @pResultadoendelse if (@cliente = 1)beginset @sentencia = N'delete from '+@pTABLA + N' where ANI = ' + @pANIEXEC sp_executesql @sentenciaset @pResultado = 3SELECT @pResultadoreturn @pResultadoendendEXEC sp_cursorcloseendMy problem is that the ouutput param @pResultado haven't got any valueand don't return anything to the java application. How can I fix thisproblem?Thanka very much for helping me!!!!
View 2 Replies
View Related
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
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
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
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
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
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
May 11, 2015
java code to retrieve the data returned by SQL server stored procedure which is of CURSOR VARYING OUTPUT type and display the details on console.
View 3 Replies
View Related
Apr 11, 2007
I'm trying to figure this out
I have a store procedure that return the userId if a user exists in my table, return 0 otherwise
------------------------------------------------------------------------
Create Procedure spUpdatePasswordByUserId
@userName varchar(20),
@password varchar(20)
AS
Begin
Declare @userId int
Select @userId = (Select userId from userInfo Where userName = @userName and password = @password)
if (@userId > 0)
return @userId
else
return 0
------------------------------------------------------------------
I create a function called UpdatePasswordByUserId in my dataset with the above stored procedure that returns a scalar value. When I preview the data from the table adapter in my dataset, it spits out the right value.
But when I call this UpdatepasswordByUserId from an asp.net page, it returns null/blank/0
passport.UserInfoTableAdapters oUserInfo = new UserInfoTableAdapters();
Response.Write("userId: " + oUserInfo.UpdatePasswordByUserId(txtUserName.text, txtPassword.text) );
Do you guys have any idea why?
View 6 Replies
View Related
Oct 11, 2007
hello all, I have a multi line textbox and want to store the content of the textbox to a database. I want to preserve the linefeeds so that I display the message as it is typed by retrieving it. Can anyone please explain how to store the linefeeds to the database. Once i store the linefeeds to the database, I can restore them using the Replace("","<br/>) method. But I can't figure out how to store the 's in the database in the first place. Thanx in advance
View 2 Replies
View Related
Mar 23, 2008
hi,
i would like to store images in my database and retirve them aswell. i have looked at examples on the net but i am finding it hard to follow and undertsand them, can anyone please give me adivse on how i can do this. i have alrady created my table in my database which has 4 fields pictureID, pictureContent, pictureType and pictureSize but i dont know where to go on from here. i want to do it in vb aswell.
please any help and advise would be much appreciated as i am stuck,
thank you
View 20 Replies
View Related
Oct 31, 2007
HI,
I want to store unicode data and retrieve them using C#.
How can I do so?
Data is saved but can not retrive. The data displayed as (??????).
Could Anybody Help me.
View 4 Replies
View Related
Jul 2, 2006
Basically i make use of FormView in Web Developer Express Edition to create and store information to the database .... what i want to do further is to capture the value within the primary key of the newly created recordset that I set it become auto increament .....this value would be stored within a session string in global condition that allowed any page able to access it .....
View 3 Replies
View Related
Feb 28, 2008
I€™ve inherited a project from one of the guys on our team who will be out sick for a while. He developed two for marshaling data between System.Drawing.Image and System.Byte(). He€™s storing the byte array data in a database image field.
I€™ve retrieved the byte array data from his database image fields and have successfully converted them to images using his ConvertByteArrayToImage method below. I have also converted and image to a byte array with his ConvertImageToByteArray method below and succfully stored the data in a database image field. However, when I retrieve the byte array data that I stored in the database the last line in his ConvertByteArrayToImage method throws an exception (Parameter is not valid). I€™ve not been able to find a working copy of his code that€™s storing the byte array data. Does anyone see anything I€™m overlooking?
Imports System.Drawing
Imports System.IO
Public Sub InsertImage(ByVal pFilename As String)
Try
Dim lImage As Image
Dim lBA() As Byte
Dim lSQL As String
Dim lQuery As Alcon.SQLServer.Database.clsQuery
Dim lParameters As New Alcon.SQLServer.Database.clsParameters
lImage = Image.FromFile(pFilename)
ConvertImageToByteArray(lImage, lBA)
' Initialization
lQuery = New Alcon.SQLServer.Database.clsQuery(mConnection)
lSQL = ""
lSQL += "INSERT INTO [TBL_PCL_LENS_DATA]("
lSQL += "[SerialNumber], "
lSQL += "[ProcessedDate], "
lSQL += "[CartonLabelImage]) "
lSQL += "VALUES ("
lSQL += "@SerialNumber, "
lSQL += "@ProcessedDate, "
lSQL += "@CartonLabelImage "
lSQL += ")"
lParameters.Add("@SerialNumber", SqlDbType.VarChar, ParameterDirection.Input, mSerialNumber)
lParameters.Add("@ProcessedDate", SqlDbType.DateTime, ParameterDirection.Input, Now)
lParameters.Add("@CartonLabelImage", SqlDbType.Image, ParameterDirection.Input, lBA)
' Execute query
lQuery.Execute(lSQL, lParameters)
Catch ex As Exception
Throw
End Try
End Sub
ConvertByteArrayToImage(ByVal pBA() As Byte, ByRef pImage As Image)
Try
' Declaration
Dim lMS As MemoryStream
' Initialization
lMS = New MemoryStream(pBA, 0, pBA.Length)
lMS.Write(pBA, 0, pBA.Length)
lMS.Position = 0
' Create image
pImage = Image.FromStream(lMS, True)
Catch ex As Exception
Throw
End Try
End Sub
ConvertImageToByteArray(ByVal pImage As Image, ByRef pBA() As Byte)
Try
' Declaration
Dim lBM As Bitmap
Dim lBR As BinaryReader
Dim lMS As New MemoryStream
' Initialization
lBM = New Bitmap(pImage)
lBM.Save(lMS, Imaging.ImageFormat.Png)
lBR = New BinaryReader(lMS)
' Create byte array
pBA = lBR.ReadBytes(lMS.Length)
Catch ex As Exception
Throw
End Try
End Sub
View 1 Replies
View Related
Feb 17, 2008
I am able to perform my Stored Procedure, but I can't figure out how to get access to the OUTPUT parameters!Here is my code: Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim mySqlDataSource As SqlDataSource = New SqlDataSource
mySqlDataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure
mySqlDataSource.ConnectionString = "my connection string"
mySqlDataSource.InsertCommand = "MyStoredProc"
mySqlDataSource.InsertParameters.Add("someId", TypeCode.Int16, 166)
mySqlDataSource.InsertParameters.Add("someNumber", TypeCode.Int16, 4)
' Add Some Parameter
Dim someParameter As Parameter = New Parameter("someParameterName", TypeCode.String)
someParameter.DefaultValue = New String("Some Value")
someParameter.Direction = Data.ParameterDirection.Output
mySqlDataSource.InsertParameters.Add(someParameter)
mySqlDataSource.Insert()
End Sub
Any ideas as to how I get the new value that was "hopefully" assigned to someParameter??? Thanks for any help
View 3 Replies
View Related
Aug 28, 2007
We are trying to create a unique key from a table with indentity set in the table. We will have a number of these tables. Therefore, we will be creating a stored procedure and passing the table as a parameter. In this example we are setting the table.
When we run the the script, the output clause from the insert should give us a unique number from the given table in the temporary table. This example stores the output in a temporary table @tTemp.
How can you use a variable table name and retrieve the output from the Insert?
declare @tTestTable varchar (20)
set @tTestTable = 'mis.test_sequence'
--DECLARE @tTestTable TABLE ( sqVal [int] IDENTITY(1,1) NOT NULL, add_date datetime)
declare @testsql varchar (4000), @testseq int
DECLARE @tTemp table (mainpk int)
set @testsql = 'DECLARE @tTemp table (mainpk int) INSERT ' + @tTestTable + ' OUTPUT INSERTED.sqVal into @tTemp VALUES (getdate() ) SELECT @testseq=mainpk FROM @tTemp'
select @testsql
EXECUTE sp_executesql @testsql, N'@testseq int output,@tTemp table (mainpk int),@tTemp table (mainpk int) ',@tTemp,@tTemp,@testseq output,@tTemp
SELECT * FROM @tTemp
Please help
Thanks Tim.
View 3 Replies
View Related
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
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
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
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
View Related
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
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
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
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
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
Jan 15, 2007
I need some help retrieving the stored procedure listed below. I would like to use a drop list to select the variable "UserName" and textboxes to send the variables "StartingDate" and "EndingDate". How do I pass these variables and then have the results show up in a gridview? Any advice would be greatly appreciated. CREATE PROCEDURE [dbo].[aspnet_starterkits_GetTimeEntryUserReportByUserNameAndDates] @UserName NVARCHAR(256), @StartingDate datetime, @EndDate datetimeASDECLARE @UserId AS UNIQUEIDENTIFIERSET NOCOUNT ONSELECT @UserId=UserId FROM aspnet_users WHERE UserName=@UserNameSELECT @UserName as UserName, SUM (timeentryDuration) AS TotalDuration,SUM (timeentryOvertime) AS TotalOvertimeHoursFROM aspnet_starterkits_TimeEntryWHERE aspnet_starterkits_TimeEntry.TimeEntryUserId=@UserId AND TimeEntryDate between @StartingDate and @EndDate
View 1 Replies
View Related
Nov 21, 2006
Hello!
I want to write a stored procedure, that will insert new row into a table and return the key of newly inserted row.
View 2 Replies
View Related
Jul 27, 2004
Hello Group
I am new to stored procedures and I have been fighting this for a while and I hope someone can help. In my sp it checks username and password and returns an integer value. I would like to retrieve some data from the same database about the user (the users first and last name and the password of the user). I can’t retrieve the data. Here is my code.
CREATE PROCEDURE stpMyAuthentication
(
@fldUsername varchar( 50 ),
@fldPassword Char( 25 )--,
--@fldFirstName char( 30 ) OUTPUT,
--@fldLastName char( 30 ) OUTPUT
)
As
DECLARE @actualPassword Char( 25 )
SELECT
@actualPassword = fldPassword
FROM
tbMembUsers
Where
fldUsername = @fldUsername
IF @actualPassword IS NOT NULL
IF @fldPassword = @actualPassword
RETURN 1
ELSE
RETURN -2
ELSE
RETURN -1
SELECT
fldFirstName,
fldLastName,
fldPassword
FROM
tbMembUsers
Where
fldUsername = @fldUsername
GO
############### login page ################
Sub Login_Click(ByVal s As Object, ByVal e As EventArgs)
If IsValid Then
If MyAuthentication(Trim(txtuserID.Text), Trim(txtpaswrd.Text)) > 0 Then
FormsAuthentication.RedirectFromLoginPage(Trim(txtuserID.Text), False)
End If
End If
End Sub
Function MyAuthentication(ByVal strUsername As String, ByVal strPassword As String) As Integer
Dim myConn As SqlConnection
Dim myCmd As SqlCommand
Dim myReturn As SqlParameter
Dim intResult As Integer
Dim sqlConn As String
Dim strFirstName, strLastName As String
sqlConn = ConfigurationSettings.AppSettings("sqlConnStr")
myConn = New SqlConnection(sqlConn)
myCmd = New SqlCommand("stpMyAuthentication", myConn)
myCmd.CommandType = CommandType.StoredProcedure
myReturn = myCmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int)
myReturn.Direction = ParameterDirection.ReturnValue
myCmd.Parameters.Add(Trim("@fldUsername"), Trim(strUsername))
myCmd.Parameters.Add(Trim("@fldPassword"), Trim(strPassword))
myCmd.Parameters.Add("@fldFirstName", strFirstName)
myCmd.Parameters.Add("@fldLastName", strLastName)
myCmd.Parameters.Add("@fldPassword", strPassword)
myConn.Open()
myCmd.ExecuteNonQuery()
intResult = myCmd.Parameters("RETURN_VALUE").Value
myConn.Close()
'If strPassword = 55555555 Then
' Session("intDefaultPass") = 1
'End If
Session("strFullName") = strFirstName & " " & strLastName
Session("strPassword") = strPassword
If intResult < 0 Then
If intResult = -1 Then
lblMessage.Text = "Username Not Registered!<br><br>"
Else
lblMessage.Text = "Invalid Password!<br><br>"
End If
End If
Return intResult
End FunctionAt this time I am not getting any errors. How can I retrieve the data that I am after?
Michael
View 4 Replies
View Related