All I Want To Do Is Feed A Varchar Into A Stored Procedure And Get An Id Back... What Am I Doing Wrong?
Dec 28, 2006
So I have been building stored procedures and things were working fine until I hit something that I am sure is incredibly simple to do; however, i have having a hell of a time with it. Here is the code:
#############################################
ALTER PROCEDURE dbo.GetUserIdForUser
@username NVARCHAR
AS
BEGIN
DECLARE @postedbyid UNIQUEIDENTIFIER
SET @postedbyid = (SELECT UserId
FROM aspnet_Users
WHERE (UserName = @username))
END
###Which returns this###
Running [dbo].[GetUserIdForUser] ( @username = jason ).
This is part of a much larger stored procedure, but this is the point of failure in the stored procedure I am trying to build. If anyone can tell me what I am doing wrong I would appreciate it. I have tried a few things that have resulted in different failures. If I remove any references to variables (delete SET @postedbyid = and replace @username with 'jason') I can get it to return a result. If I put @username in though it doesn't work. Here are the examples of those:
ALTER PROCEDURE dbo.GetUserIdForUser
@username NVARCHAR
AS
BEGIN
SELECT UserId
FROM aspnet_Users
WHERE (UserName = @username)
END
###Which returns this###
Running [dbo].[GetUserIdForUser] ( @username = jason ).
Here is a sanity check to show that the data is in fact in the database:
ALTER PROCEDURE dbo.GetUserIdForUser
AS
BEGIN
SELECT UserId
FROM aspnet_Users
WHERE (UserName = 'jason')
END
Running [dbo].[GetUserIdForUser].
I have two SQL Server stored procedures, PROC1 and PROC2. PROC1 hasabout 50 input parameters. PROC2 is the main procedure that does somedata modifications and afterwards calls PROC1 using an EXECUTEstatement.The input parameter values for PROC1 are stored in a table in mydatabase. What I like to do is passing those values to PROC1 using aSELECT statement. Currently, all 50 parameters are read and stored ina variable, and afterwards they are passed to PROC1 using:EXEC spPROC1 @var1, @var2, @var3, ... , @var50Since it is a lot of code declaring and assigning 50 variables, I waswondering if there is a possibility to run a statement like:EXEC spPROC1 (SELECT * FROM myTable WHERE id = 2)Any help on this is greatly appreciated!
please take a look at this strored proxedure and tell what is wrong with it?
CREATE PROCEDURE sp_Pictures_CreateTextPost @UserID Int, @Country varchar (2), @Description varchar (1000), @PostID int output AS BEGIN TRANSACTION INSERT INTO Pictures_TextPosts (UserID, Country, Description) VALUES (@UserID, @Country, @Description)
IF @@ERROR <> 0 ROLLBACK TRANSACTION ELSE BEGIN COMMIT TRANSACTION UPDATE Accounts_Users SET Count_InsertText = Count_InsertText + 1 Where UserID = @UserID SET @PostID = @@IDENTITY RETURN 1 END
i run this sp on MS SQL server on my local machine and it works fine. now i move the same sp to the host server and it no longer works. it is the problem with this sp as i run sql query to test it. i use the ASP.NET Enterprise Manager to insert this sp. can someone please help?
one hint: on the host server, if i add 'GO' to the end of the sp, error occured.
I have written an ASP.net app that iterates through the controls on my page if the are checkboxlist or radiobuttonlist controls, the id and value are serialized into an xml file which is sent to a SQL 2000 stored procedure where the xml data is inserted into a couple of tables.
BEGIN TRANSACTION insert into ihsurveyresponse (userid, Surveyid, Responsedate) select userid, surveyid, getdate() from openxml(@idoc, '/schema7ihsurveyresponse', 1) with (userid int, surveyid int)
if @@error<>0 begin rollback transaction select 2 return end
set @responseid = scope_identity()
INSERT INTO IHSResponsedetail (Question,Result, Response) SELECT Question, Result, @responseid FROM OPENXML (@idoc, '/schema7ihSurveyResponse/ihsresponsedetail') WITH ( [Question] char(10) '@Question', Result int '@Result') EXECUTE sp_xml_removedocument @idoc
if @@error<>0 begin rollback transaction select 3 return end
COMMIT TRANSACTION select @responseid GO
If anyone has any insight I would really appreciate the assistance.
Hiya, all! I've got this stored procedure: CREATE Procedure CheckReOccurences @PatID INT, @CPT CHAR(7),@DOS DATETIME,@DRecur SMALLINT AS DECLARE @DaysElapsed INT SET @DaysElapsed = (SELECT DATEDIFF(day,ISNULL((SELECT MAX(ServiceDate) FROM VouchersDetail WHERE PatID = @PatID AND ProcCode = @CPT),01/01/1900),@DOS)) IF @DaysElapsed < @DRecur --Patient has had procedure done too recently BEGIN PRINT 'Too Recent-Only ' + str(@dayselapsed) + ' days elapsed' RETURN 0 END ELSE --More time has elapsed since procedure last performed than is necessary BEGIN PRINT 'Not Too Recent- ' + str(@dayselapsed) + ' days elapsed' RETURN 1 END And when I run it with the exec command in Query Analyzer, filling in all parameters with proper values, I get this error message: Server: Msg 8114, Level 16, State 4, Procedure CheckReOccurences, Line 0 Error converting data type nvarchar to datetime.
Please note Line says 0, when no such line exists. I've tried running every single part separately in QA, and all runs well (the functions work, returns expected code, etc.). What's the problem?
I am having trouble getting this procedure to properly enter the data into a table. It works when I do not add the @ToName info and only ask to insert the AccountID and FromName data. When i add the @ToNamevariable, no data is inserted. I think it might have to do with the select statement. I have two TerritoryID, RegionID, DivisionID, and EmpID because I want this information to be entered for both the FromName and the ToName selected. Does anyone know what my problem is? Id appreciate any help.
CREATE PROCEDURE InsertAllThree @AccountID char(10), @FromName nvarchar(50), @ToName nvarchar(50) AS
insert into accounttransferstestmike (AccountID, FromTerritoryID, FromRegionID, FromDivisionID, FromEmpID, ToTerritoryID, ToRegionID, ToDivisionID, ToEmpID) select Accounts.AccountID, Territories.TerritoryID, Regions.RegionID, Divisions.DivisionID, Employees.EmployeeID, Territories.TerritoryID, Regions.RegionID, Divisions.DivisionID, Employees.EmployeeID From Accounts, EndoscopySqlUser.Territories INNER JOIN EndoscopySqlUser.Regions ON EndoscopySqlUser.Territories.RegionName = EndoscopySqlUser.Regions.Region INNER JOIN EndoscopySqlUser.Employees ON EndoscopySqlUser.Territories.TerritoryID = EndoscopySqlUser.Employees.TerritoryID INNER JOIN EndoscopySqlUser.Divisions ON EndoscopySqlUser.Regions.Division = EndoscopySqlUser.Divisions.DivisionID Where (Accounts.AccountID = @AccountID) and (EndoscopySqlUser.Employees.DateLeft IS NULL) AND (EndoscopySqlUser.Territories.TerritoryName=@FromN ame) AND (EndoscopySqlUser.Territories.TerritoryName=@ToNam e) GO
When I try to create the stored procedure below, I receive the following error. Can someone please let me know what is wrong with the code? The problem seems to be with the default parameter of getdate(). This is because when I comment the default parameter, it works fine. Please note that I need the @BeginDate parameter to be varchar(10). I cannot have it as datetime.
Msg 156, Level 15, State 1, Procedure test, Line 4 Incorrect syntax near the keyword 'convert'. Msg 137, Level 15, State 2, Procedure test, Line 8 Must declare the scalar variable "@BeginDate".
/* Stored Procedure*/ Create PROCEDURE test --test @BeginDate varchar(10) = convert(varchar(10),getdate(),101) as select convert(datetime,@BeginDate)
Appreciate any help that you can offer, I have three tables that need data entered when the user completes a web form, in short the tables are
dbo.JBAClient Which has JBCID as PK and other data
dbo.JBAEmployee Which has JBEID as PK and JBEClientID as a Foreign Key and other data
dbo.JBAdvert Which has JBAID as PK, JBAEmployeeID as Foreign Key , JBAClientID as Foreign Key and other data,
The stored procedure that I've tried to write is supposed to create a new client record and enter all of the relevant data into the client table (store the JBCID) then Create a new Employee Record, insert all relevant data including (JBCID into JBEClientID) and (Store both JBCID & JBEID). Finally create a new advert record and store all of the relevant data including (JBCID as JBAClientID and JBEID as JBAEmployeeID)
The stored procedure that I have written works fine until the last section, where instead of inserting JBCID into JBAClientID and JBEID into JBAEmployeeID, it inserts JBEID into JBAClientID and JBEID into JBAEmployeeID
So inserting JBEID twice and ignoring JBEClientID........
I have the following stored proceduredrop procedure ce_selectCity;set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGO-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================create PROCEDURE ce_selectCity @recordCount int output -- Add the parameters for the stored procedure here --<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2,, 0>AS declare @errNo int -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here select ciId,name from ce_city order by name select @recordCount = @@ROWCOUNT select @errNo = @@ERROR if @errNo <> 0 GOTO HANDLE_ERROR return @errNoHANDLE_ERROR: Rollback transaction return @errNoGoand i was just testing it likeProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load db.connect() Dim reader As SqlDataReader Dim sqlCommand As New SqlCommand("ce_selectCity", db.getConnection) Dim recordCountParam As New SqlParameter("@recordCount", SqlDbType.Int) Dim errNoParam As New SqlParameter("@errNo", SqlDbType.Int) recordCountParam.Direction = ParameterDirection.Output errNoParam.Direction = ParameterDirection.ReturnValue sqlCommand.Parameters.Add(recordCountParam) sqlCommand.Parameters.Add(errNoParam) reader = db.runStoredProcedureGetReader(sqlCommand) If (db.isError = False And reader.HasRows) Then Response.Write("Total::" & Convert.ToInt32(recordCountParam.Value) & "<br />") While (reader.Read()) Response.Write(reader("ciId") & "::" & reader("name") & "<br />") End While End If db.close() End SubIt returns ALL ROWS (5 in the table right now). So, recordCount should be 5. (When i run it inside SQL Server (directly) it does return 5, so i know its working there).BUT, its returning 0.What am i doing wrong??EDIT:Oh, and this is the function i use to execute stored procedure and get the readerPublic Function runStoredProcedureGetReader(ByRef sqlCommand As SqlCommand) As SqlDataReader sqlCommand.CommandType = CommandType.StoredProcedure Return sqlCommand.ExecuteReader End Function
1. I have a stored procedure, the procedure need create new category for forums, each category have their own place (what going first , what going second etc...), it's importent for me that all categories will be in property for them place, so i must read next place from table, and if not then i set it = 0 . But it's seems not working, i getting error. Can somebody tell me please what wrong ?
ALTER PROCEDURE dbo.CreateCategory ( @category_name varchar(100) )
AS declare @place int
/* setting place to zero */ SET @place = 0
/* trying to get last place and set value into @place */ SET @place = (Select max(category_place) FROM fo_categories)
/* if got nothing from table then setting value of first category */ IF (@place = 0) set @place = 1
Running dbo."CreateCategory" ( @category_name = Public forums ). Cannot insert the value NULL into column 'category_place', table 'mg_forum.dbo.fo_categories'; column does not allow nulls. INSERT fails.
2. I also would ask what SET NOCOUNT ON/OFF mean and for what i can use it ?
I've been trying to use a stored procedure to return the names of some temporary tables that i put in the tempdb table in SQL Server.
--I've been getting the following error in visual basic 6 when i try to call this: run-time error '-2147217900 (80040e14)': syntax access violation
--This is the error you get when you try to just run the code in query analyzer: [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
--what code i was trying to use (in query analyzer): {call EXEC CreateTempTables (@RQSodfil = 'a', @BulkRan = 'a', @BulkFor = 'a', @BulkJit = 'a', @ID = '0', @RQSodfilFlag = '1', @BulkRanFlag = '0', @BulkForFlag = '0', @BulkJitFlag = '0', @DeleteFlag = '0', @ErrorNum = '0')}
--code that i was trying to use in vb 6: Public Sub TemporaryTables( _ ByVal bytRQSodfilFlag As Byte, _ ByVal bytBulkRanFlag As Byte, _ ByVal bytBulkForFlag As Byte, _ ByVal bytBulkJitFlag As Byte, _ ByVal bytDeleteFlag As Byte, _ ByVal cnPlant As String)
Dim objConn As ADODB.Connection Dim objCmd As ADODB.Command Dim objRQSodfil As Parameter Dim objBulkRan As Parameter Dim objBulkFor As Parameter Dim objBulkJit As Parameter Dim objParamID As Parameter Dim objRQSodfilFlag As Parameter Dim objBulkRanFlag As Parameter Dim objBulkForFlag As Parameter Dim objBulkJitFlag As Parameter Dim objDeleteFlag As Parameter Dim objErrorNum As Parameter Dim intErrorNum As Integer
' setup command variable Set objCmd = New ADODB.Command Set objConn = New ADODB.Connection objConn.Open cnPlant objCmd.CommandText = "EXEC CreateTempTables" objCmd.CommandType = adCmdStoredProc objCmd.ActiveConnection = objConn
I am trying to get data back from stored procedure output arguments. I am in a Visual C++ enviroment calling the stored procedure via the ODBC function SQLExecDirect. When the stored procedure has only a single output argument, everything works fine. When there are multiple output arguments, I get an error "Invalid Cursor State' from the SQLFetch. This is telling me there is no result set. Below is a pseudo code fragment which shows what I am doing. I want to use SQLGetData to convert the data into C variables. I do not want to use SQLBindParameter or Parameter Markers. Sql = "DECLARE @outarg1 int DECLARE @outarg2 datetime {CALL ProcName('Inarg1','Inarg2',.......@outarg1 OUTPUT,@outarg2 OUTPUT ) } SELECT @outarg1,@outarg2 " ; rc = SQLExecDirect(hstmt,Sql,strlen(Sql) ) if( rc == SQL_SUCCESS) { rc == SQLFetch(hstmt); if(rc == SQL_ERROR) { SqlGetDiagRec(.........); } } ..... When there is one out parameter, everything works OK.
When there are multiple out parameters, I get a SQL error state 24000 which is "Invalid Cursor State". This tells me there is no result set for the Fetch to act on. The stored procedure is doing a select for each out parameter. Any Idea why I get an error when using more than one out parameter
Hi, I was wondering if anyone could offer me some advice. I am currently using a stored procedure to insert records into a database. I want to be able to retrieve the ID (primar key) from the item that has just been inserted using the stored procedure. The ID I want to get back is Meter_ID This is my stored procedure:ALTER PROCEDURE dbo.quote
I have a ms access application with a button. Clicking this button runs a sql server 2000 stored procedure. The stored procedure updates product information. When I click the button it updates the products fine. When the users (with lesser permissions than me) click the button, no error message is sent but the transactions are rolled back. Does anyone know why or how this could happen? Please let me know before I pull out the last strands of hair from my head.
Apologies if this post doesn't belong here. We are in the process of moving steps from a large SQL job to a web portal. The intent here is to allow the user to manually trigger the jobs to run. The problem I am having is in determining a way to run these job steps in the background, thus allowing the browser to take control while the step runs. These jobs can take enough time that forcing the browser to wait is not an option.The steps have been consolidated into stored procedures so in order to run a step, one would call the sproc. What I would like to do is run this in a separate thread and let that thread end on its own while the server transfers back to the client. Is something like this possible? My understanding is that the thread would be orphaned once the browser takes control again. Thanks in advance for any advice.
I have a table that has 5 values that I want to MSSQL to create a csv file for. After it is created, dump that csv file back into the database as a binary file. Is this possible?
oConn = New SqlClient.SqlConnection oConn.ConnectionString = "user id=MyUserID;data source=MyDataSource;persist security info=False;initial catalog=DBname;password=password;" oCmd = New SqlClient.SqlCommand oCmd.Connection = oConn oCmd.CommandType = CommandType.StoredProcedure oCmd.CommandText = "TestStdInfo" 'parameters block oParam1 = New SqlClient.SqlParameter("@intSchoolID", Me.ddlSchl.SelectedItem.ToString()) oParam1.Direction = ParameterDirection.Input oParam1.SqlDbType = SqlDbType.Int oCmd.Parameters.Add(oParam1) oParam2 = New SqlClient.SqlParameter("@dob", Convert.ToDateTime(Me.txbBirth.Text)) oParam2.Direction = ParameterDirection.Input oParam2.SqlDbType = SqlDbType.DateTime oCmd.Parameters.Add(oParam2) oParam3 = New SqlClient.SqlParameter("@id", Me.txbID.Text) oParam3.Direction = ParameterDirection.Input oParam3.SqlDbType = SqlDbType.VarChar oCmd.Parameters.Add(oParam3) oConn.Open() daStudent = New SqlClient.SqlDataAdapter("TestStdInfo", oConn) dsStudent = New DataSet daStudent.Fill(dsStudent) 'This line is highlighted when error happens oConn.Close()The error I am getting :Exception Details: System.Data.SqlClient.SqlException: Procedure 'TestStdInfo' expects parameter '@intSchoolID', which was not supplied.I am able to see the value during debugging in the autos or command window. Where does it dissapear when it comes to Fill?Could anybody help me with this, if possible fix my code or show the clean code where the procedure called with multiple parameters and dataset filled.Thank you so much for your help.
In the SqlDbType enumeration there is no value for the new (max) types, only varchar. If Im passing a large string, it will get cut off at 8K. So how do I specify my varchar parameter as being of the max type ?
Hi All, I have a Stored procedure as below.. create procedure sp_sample @name varchar(12) as begin set @name = (select name from mytable where id = 1) select @name end
how can i return the varchar value from the above Stored procedure? I want to capture it in SQLFetch ODBC call.
I'm having the following (abbreviated) stored procedure:
Code Snippet
CREATE PROCEDURE proc_SomeSmartName @SomeVariable VARCHAR AS BEGIN SELECT COUNT(ID) AS SomeLabel, SomeField FROM SomeTable GROUP BY SomeField HAVING SomeField = @SomeVariable END Now my problem: It doesn't seem to work if I give the @SomeParameter a string to work with, neither via SqlCommandObject nor directly in the Management Studio. The following returns zero rows:
Code Snippet
DECLARE @return_value int EXEC @return_value = [dbo].[proc_SomeSmartName] @SomeVariable = 'MyText' SELECT 'Return Value' = @return_value Funny enough, when I have the following query, it works perfectly:
Code Snippet
SELECT COUNT(ID) AS SomeLabel, SomeField FROM SomeTable GROUP BY SomeField HAVING SomeField = 'MyText' Returning one row as it should. SomeField is an NVarChar field, but I tried casting it to VarChar without any benefit, and I also supplied the parameter as NVarChar to test, both without further success. And 'MyText' does exist in the database, in both cases when I run the stored procedure and when I run the SQL statement directly.
I have a stored procedure which returns a count of products and a limited number of rows from a query.
I am using SQL Server 2005 and calling the procedure in asp.net
The procedure is as follows
Code Snippet
GO ALTER PROCEDURE [dbo].[GetProductsByCategoryId] @Category VARCHAR(255), @Range INT, @PageIndex INT, @NumRows INT, @CategoryName nvarchar(255) OUTPUT, @CategoryProductCount INT OUTPUT AS
BEGIN
/* Get product count */ SELECT @CategoryProductCount=(SELECT COUNT(*) FROM Products LEFT JOIN tblVar on Products.ProductID = tblVar.prodidvar WHERE Products.Category=@Category AND Products.Range=@Range)
/* get full list of products */ With ProductEntries as ( SELECT ROW_NUMBER() OVER (ORDER BY Products.ProductID, tblVar.idvar ASC) as Row, field1, field2 FROM Products LEFT JOIN tblVar on Products.ProductID=tblVar.prodidvar WHERE Range=@Range AND Category = @Category )
/*get only needed rows */ SELECT field1, field2 FROM ProductEntries WHERE Row Between @startRowIndex and @startRowIndex+@NumRows-1
END
The problem seems to be with the line
AND Category = @ Category in the query to make the ProductEntries
If I take this query and run it in an SQL pane I need to enclose the argument for @Category in single quotes. If I try to do this in the procedure it simply searchs for @Category as a string rather than the value of @Category.
The query returns and displays results with no problems without this line, and also if it is returning a result set that has no values in tblVar to join to.
Also if I run the query on just the Products table removing the left join it will return results with no problems.
Thanks to anyone who can help!
And I apologise if it is something simple but asp and SQL Server is not my usual coding platform.
in the statement, where @sql is defined as DECLARE @sql varchar(Max). the problem is that this statement produces results that are in excess of 8000 characters and the results are truncated. Is there anyway to avoid this? I know that it's not possible to user ntext/text as a local variable, and if i try to return the result as an ouput paramater, only the first result is returned.
my code is based off of this article http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables Thanks for any suggestions.
Hi , I am trying to write a stored procedure (i have given it below).i am basically trying to join 4 strings into one based on some if conditions.But the result gives only the intially assaigned string and rest are not getting concatenated.i have provided teh stored procedure below along with the inputs and result i got.Can anyone Please help me to acheive this set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[TestSearch] @distributorId int, @locationId int, @orderTypeId int, @fromDate Datetime = NULL, @toDate Datetime = NULL, @OrderStatus varchar(500) = NULL, @TaxAuthority varchar(500) = NULL, @TaxStampType varchar(500) = NULLASBEGINDeclare @SQL varchar(8000)Set @SQL ='select * from Orders AS a INNER JOINOrderLines AS b ON a.Id = b.FkOrder INNER JOINTaxStampTypes AS c ON b.FkTaxStampType = c.Id where ''' +CONVERT(VARCHAR(8),@fromDate ,1) + ''' <= CONVERT(VARCHAR(8),a.OrderDate ,1) and ''' +CONVERT(VARCHAR(8),@toDate ,1) + '''>= CONVERT(VARCHAR(8), a.OrderDate,1)and a.fkordertype = '+ convert(varchar(1),@orderTypeId) +'anda.FkDistributor = ('+ convert(varchar(50), @distributorId)+',a.FkDistributor)anda.FkLocation in ('+convert(varchar(10),@locationId)+',a.FkLocation)and'IF(@OrderStatus != null)Beginset @SQL= @SQL + 'a.FkOrderState in ('+ @OrderStatus +') and' EndIF(@TaxAuthority!= null)Beginset @SQL = @SQL +'a.FkTaxAuthority in ('+@TaxAuthority+') and'EndIF(@TaxStampType!= null)Beginset @SQL = @SQL + 'c.id in ('+ @TaxStampType+ ')and'End--Execute (@SQL1)select (@SQL);ENDHere is the Input Given to stored Procedure for executing:DECLARE @return_value intEXEC @return_value = [dbo].[TestSearch] @distributorId = 1002, @locationId = 3, @orderTypeId = 1, @fromDate = N'06/10/07', @toDate = N'10/10/07', @OrderStatus = N'2', @TaxAuthority = N'1000', @TaxStampType = N'1000'SELECT 'Return Value' = @return_valueHere Is The Output i get when I execute the stored procedure: select * from Orders AS a INNER JOIN OrderLines AS b ON a.Id = b.FkOrder INNER JOIN TaxStampTypes AS c ON b.FkTaxStampType = c.Id where '06/10/07' <= CONVERT(VARCHAR(8),a.OrderDate ,1) and '10/10/07'>= CONVERT(VARCHAR(8), a.OrderDate,1) and a.fkordertype = 1 and a.FkDistributor = (1002,a.FkDistributor) and a.FkLocation in (3,a.FkLocation) and--Ajay
I have 1 files, one is .sql and another is stored procedure. SQL file will call the stored procedure by passing the variables setup in the SQL file. However, everything ran well except at the end, I try to get the return value from SP to my SQL file ... which will send notification email. But, I get the following msg ... I am not sure how to fix this ... help!!! :( Syntax error converting the varchar value 'ABC' to a column of data type int. SQL file ====================== DECLARE @S AS VARCHAR(1000) EXEC @S = PDT.DBO.SP_RPT 'ABC' SELECT CONTENT = @S -- this is the value I am trying to pass as content of the email EXEC PRODUCTION.DBO.SENDEMAIL 'xxx@hotmail.com', 'Notification', @S ====================== Stored Procedure ====================== CREATE procedure sp_RPT( @array varchar(1000) ) AS DECLARE @content AS VARCHAR(2000) SET @content = 'RPT: ' + @array + ' loaded successfully ' SET @array = 'ABC'RETURN CONVERT(VARCHAR(1000),@array) GO
In my .NET app I have a search user control. The search control allows the user to pick a series of different data elements in order to further refine your display results. Typically I store the values select in a VarChar(5000) field in the DB. One of the items I recently incorporated into the search tool is a userID (GUID) of the person handling the customer. When I go to pass the selected value to the stored proc
objUpdCommand.Parameters.Add("@criteria", SqlDbType.VarChar).Value = strCriteria; I get: Failed to convert parameter value from a String to a Guid. Ugh! It's a string, dummy!!! I have even tried: objUpdCommand.Parameters.Add("@criteria", SqlDbType.VarChar).Value = new Guid(strCriteria).ToString(); and
objUpdCommand.Parameters.Add("@criteria", SqlDbType.VarChar).Value = new Guid(strCriteria).ToString("B"); but to no avail. How can I pass this to the stored proc without regard for it being a GUID in my app?
I have a problem I can't seem to find the solution with this aweful limitations on VARCHAR fields of 255.
Within a stored procedure called Store_Check, I need to dynamically build a string (@string) using VARCHAR(255) since the text datatype can't be used in a stored procedure.
So,this string is built according to whether the Store ID is NOT NULL. So if the StoreID is not null, I start building this string 'Exec Update_Store_Address @StoreID1, @address2'. There are 20 StoreID's passed into Store_Check. IF all 20 StoreID's are not NULL, the executed String greatly exceeds 255 because the string winds up looking like this
I am not executing this string within the StoredCheck procedure. It needs to be passed as ONE string to a VB program and it gets executed by the VB program. Even if I create 4 local variables and concatenate them, it stops at the 255th character.
Also, a local varialbe of type TEXT cannot be declared within stored procedure.
how to execute this stored procedure to decode a varchar hexadecimal string? My SQL syntax stills have faded with the sands of time... I want to do a select * from the users table and decode the Password field (but not update) in the process.
Code: USE [byrndb] GO /****** Object: StoredProcedure [dbo].[sp_GetLikeJobsByJobNumber] Script Date: 08/28/2012 15:17:28 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
[Code] ....
Why when I run
Code: USE [byrndb] GO DECLARE@return_value int EXEC@return_value = [dbo].[sp_GetLikeJobsByJobNumber] @number = N'23913' SELECT'Return Value' = @return_value GO
Am I getting an "Msg 8114, Level 16, State 5, Procedure sp_GetLikeJobsByJobNumber, Line 16
Error converting data type varchar to numeric." error? Line 16 is "@number varchar = null"
I'm having a hard time to getting back an xml data back from a stored procedure executed by an Execute SQL task.
I'm passing in an XML data as a parameter and getting back resulting XML data as a parameter. The Execute SQL task is using ADO connection to do this job. The two parameters(in/out) are type of "string" and mapped as string.
When I execute the task, I get the following error message.
[Execute SQL Task] Error: Executing the query "dbo.PromissorPLEDataUpload" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 2 ("@LogXML"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I also tried mapping the parameter as XML type, but that didn't work either.
If anyone knows what's going on or how to fix this problem please let me know. All I want to do is save returning XML data in the parameter to a local package variable.
Hi,I am trying to write a method which needs to call a stored procedure and then needs to get the response of the stored procedure back to the variable i declared in the method. private string GetFromCode(string strWebVersionFromCode, string strWebVersionString) { //call stored procedure } strWebVersionFromCode = GetFromCode(strFromCode, "web_version"); // is the var which will store the response.how should I do this?Please assist.
I get the following error for this procedure:Server: Msg 156, Level 15, State 1, Procedure Trig_Ins_TrackingNumbers,Line 6Incorrect syntax near the keyword 'IF'.CREATE TRIGGER Trig_Ins_TrackingNumbers ON TrackingNumbersINSTEAD OF INSERTASBEGINIF (SELECT ISNUMERIC(InvoiceNumber) FROM inserted)IF(EXISTS (SELECT I.InvoiceNumberFROM Invoices I, inserted InsWHERE I.InvoiceNumber=CAST(Ins.InvoiceNumber AS INT)))INSERT INTO TrackingNumbers(InvoiceNum,InvoiceNumber,TrackingNumber)SELECT CAST(InvoiceNumber AS INT), InvoiceNumber, TrackingNumberFROM insertedEND
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie: exec dbo.AddProduct_Insert 'widget1' which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) I want to simply the insert to perform (in one sproc): SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example). My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
