T-Sql Stored Procedure Comparing String
Feb 24, 2008
Hi
I have a problem trying to compare a string value in a WHERE statement. Below is the sql statement.
ALTER PROCEDURE dbo.StoredProcedure1
(
@oby char,
@Area char,
@Startrow INT,
@Maxrow INT,
@Minp INT,
@Maxp INT,
@Bed INT
)
AS
SELECT * FROM
(
SELECT row_number() OVER (ORDER BY @oby DESC)
AS rownum,Ref,Price,Area,Town,Bed
FROM [Houses]
WHERE ([Price] >= @Minp) AND ([Price] <= @Maxp) AND ([Bed] >= @Bed) AND ([Area] = @Area))
AS A
WHERE A.rownum BETWEEN (@Startrow) AND (@Startrow + @Maxrow)
The problem is the Area variable if i enter it manually it works if i try and pass the variable in it doesn't work.
If i change ([Area] = @Area) to ([Area] = 'The First Area') it is fine. The only problem i see is that the @Area has spaces, but i even tried passing 'The First Area' with the quotes and it still didnt work.
Please help, its got to be something simple.
Thanks In Advance
View 2 Replies
ADVERTISEMENT
Aug 14, 2006
Basically I have two strings. Both strings will contain similar data because the 2nd string is the first string after an update of the first string takes place. Both strings are returned in my Stored Procedure
For example:String1 = "Here is some data. lets type some more data"String2 = "Here's some data. Lets type some data here"I would want to change string2 (inside my Stored Procedure) to show the changed/added text highlighted and the deleted text with a strike though.
So I would want string2 to look like thisstring2 = "Here<font color = "#00FF00">'s</font> <strike>is</strike> some data. <font color = "#00FF00">L</font>ets type some <strike>more</strike> data <font color = "#00FF00">here</font>"
Is there an way to accomplish this inside a stored procedure?
View 2 Replies
View Related
Dec 11, 2007
I have large stored procedures in SQL Server 2005 that often get updated. Sometimes it is very difficult to recognize how one change could impact the overall results. I would like to have some sample input that I could test during changes and see it compare the data results before & after my changes. This would help me quickly identify if the results are fine to pass through.
So basically I would like an easy way to compare the results of 2 stored procedures. Any suggestions or utilites that would help me do this?
View 3 Replies
View Related
Dec 10, 2007
I have large stored procedures in SQL Server 2005 that often get updated. Sometimes it is very difficult to recognize how one change could impact the overall results. I would like to have some sample input that I could test during changes and see it compare the data results before & after my changes. This would help me quickly identify if the results are fine to pass through.
So basically I would like an easy way to compare the results of 2 stored procedures. Any suggestions or utilites that would help me do this?
View 6 Replies
View Related
Apr 5, 2006
Not sure this is the right forum as I'm not sure quite what the problem is, but I have a feeeling it's the stored procedure that I'm using to replace the SQL string I used previously.I have a search form used to find records in a (SQL Server 2005) db. The form has a number of textboxes and corresponding checkboxes. The user types in a value they want to search for (e.g. search for a surname) and then selects the corresponding checkbox to indicate this. Or they can search for both surname and firstname by typing in the values in the correct textboxes and selecting the checkboxes corressponding to surname and firstname.The code to make this work looks like this:---------------------------------------- Dim conn As SqlConnection Dim comm As SqlCommand Dim param As SqlParameter Dim param2 As SqlParameter Dim param3 As SqlParameter Dim param4 As SqlParameter Dim objDataset As DataSet Dim objAdapter As SqlDataAdapter conn=NewSqlConnection("blah, blah") comm = New SqlCommand 'set properties of comm so it uses conn & recognises which stored proc to execute comm.Connection = conn comm.CommandText = "SPSearchTest3" comm.CommandType = CommandType.StoredProcedure 'create input parameter, set it's type and value param = comm.CreateParameter param.ParameterName = "@empid" param.Direction = ParameterDirection.Input param.Value = txtPatID.Text param2 = comm.CreateParameter param2.ParameterName = "@LastName" param2.Direction = ParameterDirection.Input param2.Value = txtSurname.Text comm.Parameters.Add(param) comm.Parameters.Add(param2) conn.Open() objAdapter = New SqlDataAdapter(comm) objDataset = New DataSet objAdapter.Fill(objDataset) dgrdRegistration.DataSource = objDataset dgrdRegistration.DataBind() conn.Close()------------------------------------While the stored procedure is this:------------------------------ @EmpID int, @LastName nvarchar(20) ASSELECT EmployeeID, LastName, Firstname, BirthDate, Address, title, addressFROM employeesWHERE (DataLength(@EmpID) = 0 OR EmployeeID = @EmpID)AND (DataLength(@LastName) = 0 OR LastName = @LastName)------------------------------This will work if I search using EmployeeID and Surname or only by EmployeeID, but I don't get any results if I search only for Surname, even though I know the record(s) exits in the db and I've spelled it correctly. Can someone point out where I'm going wrong?(Incidentally if I have a procedure with has only one parameter 'surname' or 'employeeID', it works fine!)Thanks very much and sorry about the long-winded post.
View 6 Replies
View Related
Mar 29, 2004
Up till now I've used SP's for updates and only ever needed to return error messages.
Now I have an SP that checks and validates something and has to return a string containing the result, (always a string/varchar!)
It works fine in Query Analyzer, I just need a demo of how to incorporate it into a VB app.
Hope that makes sense.
Thanks
Mark
View 4 Replies
View Related
Nov 28, 2005
hi,i have a stored procedure that is used to insert the employee data into a EMPLOYEE table.now i am passing the employee data from sqlCommand.i have the XML string like this'<Employee><Name>Gopal</Name><ID>10157839</ID><sal>12000</sal><Address>Khammam</Address></Employee>' when i pass this string as sql parameter it is giving an error. System.Data.SqlClient.SqlException: XML parsing error: A semi colon character was expectedbut when i execute the stored procedure in query analyzer by passing the same parameter. it is working.please reply me on gk_mpl@yahoo.co.in
View 1 Replies
View Related
Apr 3, 2008
This procedure gives a error : " Msg 245, Level 16, State 1, Procedure YAMAN, Line 16
Conversion failed when converting the nvarchar value 'user' to data type int. "
How can i return string value
ALTER procedure [dbo].[YAMAN]
(@username varchar(20),@active varchar(20))
as
begin
if exists (select username from aspnet_Users where username=@username)
begin
if @active=(select active from aspnet_Users where username=@username)
return 'already exist'
else
begin
update aspnet_Users set active=@active where username=@username
return 'update'
end
end
else
return 'user does not exist'
end
Yaman
View 2 Replies
View Related
Nov 2, 2006
I am somewhat new to the world of programming with SQL Server and was wondering if this could be done. Well I know it can be done but was wondering how it might be done.
I have a DTS package created to import a table from and AS400 server. What I need to do is take one field and parse that field into 5 different values for 5 new fields.
Here is what I know needs to be done but not sure how to put into the procedure.
CREATE proc ChangeHIS
as
--Declare Variables
Declare @LastName varchar,
@FirstName varchar,
@MI varchar,
@ID varchar,
@Dept varchar,
@intCount int,
@UserName varchar,
@strTemp varchar
--Create Temporary Table
CREATE TABLE [EmployeeAudit].[dbo].[tmpTable] (
[UPUPRF] varchar (10),
[UPTEXT] varchar (50)
)
select [UPUPRF], [UPTEXT] from tblHIS into tmpTable
GO
And something dealing with the below code as well.
@tmpString = RTRIM(LTRIM(@tmpString))
If charindex(@tmpString, ",") > 0
--'Manuel, Michael J - 78672 - SR MIS SUPPORT SPEC'
@LastName = Left(@tmpString, charindex(@tmpString, ","))
@tmpString = RTRIM(LTRIM(Right(@tmpString, Len(@tmpString) - charindex(@tmpString, ",") + 1)))
--'Michael J - 78672 - SR MIS SUPPORT SPEC'
@FirstName = Left(@tmpString, charindex(@tmpString, " "))
@tmpString = RTRIM(LTRIM(Right(@tmpString, Len(@tmpString) - charindex(@tmpString, " ") + 1)))
If charindex(@tmpString, "-") > 1
--'J - 78672 - SR MIS SUPPORT SPEC'
@MI = Left(@tmpString, 1)
@tmpSting = RTRIM(LTRIM(Right(@tmpString, Len(@tmpString) - 2)
End
--'- 78672 - SR MIS SUPPORT SPEC'
@ID = Left(@tmpString, charindex(@tmpString, " - "))
@tmpString = RTRIM(LTRIM(Right(@tmpString, Len(@tmpString) - charindex(@tmpString, " - ") + 3)))
--'SR MIS SUPPORT SPEC'
@Dept = @tmpString
End
Hope someone can point me in the right direction
View 13 Replies
View Related
Nov 19, 2007
I have SQL table (tblUsers) and one of the fields holds the email address. I want to step through each record build a multiple email string to send to a lot of people. It would look like this
Str_email = Me@hotmail.com;Andy@Hotmail.com;Fred@Hotmail.com
I then want to pass Str_email back to an asp.web page
Can this be done in a stored procedure ?
View 5 Replies
View Related
Jul 20, 2005
Is there anyway to extract part of a string in a stored procedureusing a parameter as the starting point?For example, my string might read: x234y01zx567y07zx541y04zMy Parameter is an nvarchar and the value is: "x567y"What I want to extract is the two charachters after the parameter, inthis case "07".Can anyone shed some light on this problem?Thanks,lq
View 4 Replies
View Related
Dec 8, 2006
I'm passing a comma delimited string to my SP, e.g.:"3,8,10,16,23,24"I need to retreive each number in this string and for every number found I need to execute some sode, say add "AND SportID="+numberfoundHow can I do that?
View 6 Replies
View Related
May 27, 2005
Hello,
I was wondering if it's possible to pass in a comma separated string
"12,14,16,18" and use it in a stored procedure with "IN" like this:
@SubRegions varchar(255) <-- my comma separated string
SELECT *
FROM myTable
WHERE tbl_myTable.SubRegionID IN (@SubRegions)
It tells me it has trouble converting "'12,14,16,18'" to an INT. :(
View 2 Replies
View Related
Aug 2, 2005
Hello, I'm trying to pass a simple string value to a stored procedure and just can't seem to make it work. I'm baffled since I know how to pass an integer to a stored procedure. In the example below, I don't get any compile errors or page load errors but my repeater doesn't populate (even though I know for certain the word "hello" is actually in the BlogTxt field in the db. If I change the stored procedure to say...WHERE BlogTxt LIKE '%hello%'then the results do indeed show up in the repeater.I ultimately would like to pass text from a textbox control or maybe even a querystring to the stored procedure. Then I'll move on to passing multiple "keywords" to it. :)My relevant code is below. Thanks in advance for any help.*******************ViewData.ascx.vb file*******************Private strSearch As String
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.LoadTry Dim objBlogController As New BlogController 'for testing purposes strSearch = "hello" repeaterSearchResults.DataSource = objBlogController.SearchBlog(strSearch) repeaterSearchResults.DataBind()Catch exc As Exception ProcessModuleLoadException(Me, exc)End TryEnd Sub----------------------------------------
*******************Controller.vb file*******************
Public Function SearchBlog(ByVal strSearch As String) As ArrayList Return CBO.FillCollection(DataProvider.Instance().SearchBlog(strSearch), GetType(BlogInfo)) End Function----------------------------------------
******************* DataProvider.vb file*******************
Public MustOverride Function SearchBlog(ByVal strSearch As String) As IDataReader----------------------------------------
*******************SqlDataProvider.vb file*******************
Public Overrides Function SearchBlog(ByVal strSearch As String) As IDataReader Return CType(SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner & ObjectQualifier & "SearchBlog", strSearch), IDataReader)End Function----------------------------------------
*******************Stored Procedure*******************
CREATE PROCEDURE dbo.SearchBlog @strSearch varchar(8000)AS
SELECT ItemID, PortalID, ModuleID, UserID, BlogTxt, DateAdd, DateModFROM BlogWHERE BlogTxt LIKE '%@strSearch%'GO----------------------------------------
View 3 Replies
View Related
Jun 14, 2006
Hello, I have a question on sql stored procedures.I have such a procedure, which returnes me rows with ID-s.Then in my asp.net page I make from that Id-s a string likeSELECT * FROM [eai.Documents] WHERE CategoryId=11 OR CategoryId=16 OR CategoryId=18.
My question is: Can I do the same in my stored procedure? Here is it:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[eai.GetSubCategoriesById]
(
@Id int
)
AS
declare @path varchar(100);
SELECT @path=Path FROM [eai.FileCategories] WHERE Id = @Id;
SELECT Id, ParentCategoryId, Name, NumActiveAds FROM [eai.FileCategories]
WHERE Path LIKE @Path + '%'
ORDER BY Path
Thank youArtashes
View 2 Replies
View Related
Aug 19, 2004
I have a stored procedure called from ASP code, and when it is executed, the stored procedure takes in a date as an attribute.
But since the stored procedure call is really just a string in the code, it is taking in the value as a string. So in my stored procedure, I want to convert a string value to a date value.
Any idea how this is done?
View 1 Replies
View Related
Jul 23, 2005
Hi,I would like to check if a string value exist in a string in sqlserver stored procedure, e.g.set @testString = 'this is my test document.'if (@testString contains 'test')begin.....endHow do I do this in sql server stored procedure?Thanks,June...
View 1 Replies
View Related
May 4, 2007
Hello! This is my scenario...
Development - Visual Studio 2005
Database - MS SQL 2005
I have written a stored procedure that has a output parameter whose type is NVARCHAR(MAX). Then I use C# to wrap this stored procedure in OLEDB way. That means the OleDbType for that parameter is VarWChar.
This works fine if the string size is less than 4000. If more than 4000, the remaining part will be chopped off. The situation gets worst if I replace VarWChar with LongVarWChar. There is no error nor warning at compiling time. But there is an exception at run time.
So... Does anyone here can help me out? Thanks in advance.
View 6 Replies
View Related
Jul 19, 2007
Has anyone been able to find a way to use a stored procedure with a dynamic connection string?
View 4 Replies
View Related
Oct 14, 2007
I have a multivalued parameters populated from a dataset with country codes: US,CA,HK etc... I tried to pass the parameter to the stored procedured but that did not work. I did some research and found out that I won't be able to do that.
Writing Queries that Map to Multivalued Report Parameters
http://msdn2.microsoft.com/en-us/library/ms155917.aspx
You can define a multivalued parameter for any report parameter that you create. However, if you want to pass multiple parameter values back to a query, the following requirements must be satisfied:
The data source must be SQL Server, Oracle, or Analysis Services.
The data source cannot be a stored procedure. Reporting Services does not support passing a multivalued parameter array to a stored procedure.
The query must use an IN statement to specify the parameter.
If I am not able to pass the parameter that way, what is the best way to accomplish my goal?
Thanks,
Elie
View 5 Replies
View Related
Aug 26, 2007
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?
View 1 Replies
View Related
Sep 20, 2007
I have tried to code a href link to bind the data from a Stored Procedure, as shown below.This herf string failed. How to use these special characters, ' < " > ) ?Or how can I use sb(string builder), which I have used successfully for display the data.
TIA,Jeffrey<%cmd.CommandText = "MyProgramsA"cmd.CommandType = CommandType.StoredProcedurecmd.Parameters.Add(New SqlParameter("@meid", SqlDbType.Int))cmd.Parameters("@meid").Value = EmpIdcmd.Connection = sqlConnection2sqlConnection2.Open()reader4 = cmd.ExecuteReader()While reader4.Read()%>
<a href="http://sca3a56/'<% reader4(i).ToString() %>' "><% Response.Write(sb3) %></a><br />
<br />
View 1 Replies
View Related
Jun 28, 2004
if i send the string
2,3,4,5 to a stored procedure...
is there a way i could split those values for input into the database? no, right? i would need a seperate stored procedure that would take each value one at a time...correct?
View 7 Replies
View Related
Apr 10, 2015
SP to parse a delimited string and insert the result in a table. I am using SQL Server 2008 R2. I have 2 tables - RawData & WIP. I have Robots on a manufacturing line capable of moving data to a DB. I move the raw data to RawData. On insert [RawData], I want to parse the string and move the contents to WIP as indicated below. I will run reports against the WIP Table.
Also, after the string is parsed, I'd like to change the Archive column, the _0 at the end of the raw string to 1 in the WIP table to indicate a successful parse.
Sample Strings - [RawData Table]
04102015_114830_10_013_9_8_6_99999_Test 1_1_0
04102015_115030_10_013_9_8_6_99999_Test 2_1_0
Desired Output - [WIP Table]
Date Time Plant Program Line Zone Station BadgeID Message Alarm Archive
-----------------------------------------------------------------------------------
04102015 114830 10 13 9 8 6 99999 Test 1 1 1
04102015 115030 10 13 9 8 6 99999 Test 2 1 1
View 16 Replies
View Related
Sep 27, 2007
Using SQL Server 2000...I wrote a wrapper to call a sub proc (code provided below). Theintended varchar value returned in the output parameter of each procis a string implementation of an array.(The string separates elements by adding a period after each value.e.g. 1. 2. 3. 4. 5. etc., although my simplified example only createstwo elements.)My vb.net calling code parses the returned string into individualelements.I TESTED BOTH PROCS FIRST:The wrapper returns 'hello' when I test it by insertingSELECT @lString='hello'before the GO, so I believe it is called properly.The sub_proc returns the "array" I want when I call it directly.THE PROBLEM: When I call the wrapper, and expect it to call sub_proc,it returns a zero.In fact, when I assign a literal (like 'hello') to @lString insub_proc, 'hello' is not returned.So the wrapper is not calling the sub_proc, or the sub_proc is notreturning an output value.OR...I have read about some issues with OUTPUT string parameters beingtruncated or damaged somehow when passed. I doubt this is theproblem, but I'm open to anything.I want to use the wrapper because, when it's finally working, it willcall several sub_procs andreturn several output values.Any thoughts? Thanks for looking at it! - BobThe Wrapper:-----------------------------------------------------------------CREATE PROCEDURE wrapper@lString varchar(255) OUTASEXEC @lString = sub_proc @CommCode, @lString OUTGO-----------------------------------------------------------------The subordinate procedure:-----------------------------------------------------------------CREATE PROCEDURE sub_proc@lString varchar(255) OUTASDECLARE @var1 int,@var2 intSELECT @var1 =(SELECT count(mycolumn)FROM mytableWHERE condition=1)SELECT @var2 =(SELECT count(mycolumn)FROM mytableWHERE condition=2)/* If @var1 returns 5 and @var2 returns 7, Then @lString below wouldbe "5. 7." */SELECT @lString = STR(@var1) + '.' + STR(@var7) + '.'GO-----------------------------------------------------------------
View 3 Replies
View Related
Jul 20, 2005
Hallo !I have a Table with a column "ordernumber"ordernumberA12A45A77A88Is it possible to create a stored procedure which makes a string of these column ?Result: string = ('A12','A45','A77','A88')Thanks !aaapaul
View 3 Replies
View Related
Jul 20, 2005
I have a table tblCustomers in a one-to-many relationship with tabletblProducts.What I want to do is to create a stored procudure that returns a listof each customer in tblCustomers but also creates a field showing astring (separated by commas)of each matching record in tblProducts.So the return would look like:CustID Customer ProductList1 Smith Apples, Oranges, Pears2 Jones Pencils, Pens, Paperetc...Instead of:CustID Customer Product1 Smith Apples1 Smith Oranges1 Smith Pears2 Jones Pencils2 Jones Pens2 Jones PaperWhich is what you get with this:SELECT tblCusomers.CustID, tblCusomers.Customer,tblProducts.ProductFROMtblCusomers INNER JOINtblProducts ONtblCustomers.CustID = tblProducts.CustIDI'd appreciate any help!lq
View 6 Replies
View Related
Jul 5, 2007
Hi,
Using SSIS 2005 how is it possible to loop through a folder on the network, look at each file, pass the data inside each file as a string into a stored procedure.
Thanks
View 1 Replies
View Related
Sep 26, 2006
Is there a sample way to define string constant which every stored procedure can use in SQL 2005 ? 1. In stored procedure A, there is select a1,a2,a3,a4 from mytable where usename='qaz'2. In stored procedure B, there isselect a1,a2,a3,a4 from mytable where VisitNumber>33. I hope there is a sample way to define string constant such as: constant mystring='a1,a2,a3,a4'4. So I can use this string constant both stored procedure A and stored procedure bsuch as:select mystring from mytable where usename='qaz' select mystring from mytable where VisitNumber>35. How can I do that? is there a sample way? Mnay Thanks!
View 1 Replies
View Related
Dec 5, 2006
Hi all,I have created this simple Stored procedure. But it gives me wrong result when I pass a parameter to it. But if I hard
code it, it gives me the right result.
The I check if the field value of 'Email' is exactly equal to the parameter '@Email'.
The field 'Email' is varchar, and CID is integer.
CREATE PROCEDURE EmailExists @Email varcharASSELECT CIDFROM CustomersWHERE Customers.Email = @Emailreturn
Instead, if I check the value directly, it gives me correct answer. The the following code works fine when I typethe Email directly in the code.
CREATE PROCEDURE EmailExists @Email varcharASSELECT CIDFROM CustomersWHERE Customers.Email = 'tomyseba@yahoo.com'
return
Can anyone tell me the reason for it.
Thanking you in advance
Tomy
View 2 Replies
View Related
May 5, 2014
In the below procedure definition, i need to find a way parse the definition and get the list of places where the where clause is being used.
SELECT DISTINCT
FC.CASE_ID,
UPPER(FC.CASE_NUMBER) AS CASE_NUMBER,
UPPER(REPLACE(FC.CASE_SHORT_TITLE,CHAR(13)+CHAR(10),'')) AS CASE_SHORT_TITLE,
FC.CASE_STATUS_DESCR,
FC.CASE_TYP_DESC, FC.CASE_SUB_TYP_DESC,
[Code] ....
In the above query there are more than one places and the where clause may not have the same string the where clause it can be with a space between the "=" and the value in single quotes.
Result set should be in the below format:
TABLE NAME Column Name VALUE
CFG_ELEMENTS ELEMENT_NAME REPORT_CONSOLIDATEDCASES_CASERELATEDTYPID
View 9 Replies
View Related
Apr 16, 2015
We have a legacy database that have hundreds of stored procedures.
The previous programmar uses a string like servername.databasename.dbo.tablename in the stored procedures.
We now have migrated the database to a new server. The old server is either needed to be replaced by the new server name, or remove it.
I don't know why he used servername as part of the fully qualified name, we don't use linked servers. So I think better removing the servername in all the stored procedures.
I know I can do a generate script, and replace the text and then use alter procedure to recreate all the stored procedures. But since hundreds of them, is there a programmatically way to replace them?
View 2 Replies
View Related
Nov 20, 2014
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.
CREATE PROCEDURE spPasswordDecode (@hex varchar (100), @passwordtext varchar (100) output)
AS
declare @n int,
@len int,
@str nvarchar(500),
@output varchar(100)
[code]....
View 4 Replies
View Related