Stored Procedure Using WildCards

Dec 12, 2007



I have an ASP.NET application where I am using a drop down list which is populated from another table. I have initialized the drop down with a "All" with the value of "%" field and then appended the rest of the data from the table.

I wrote a basic stored procedure which doesn't work exactly the way I want it to:


CREATE PROCEDURE dbo.spGetHistory

@StartDate datetime,

@EndDate datetime,

@MessageCode char(2)



AS



SELECT *

FROM table_name

WHERE (update_date between @StartDate AND @EndDate) AND (message_code LIKE @MessageCode)

If I select any item except for "All" the stored procedure brings back exactly what I want. If I select "All", no rows are returned. I have searched around the internet for a simple solution to this with no luck. Any ideas?

View 4 Replies


ADVERTISEMENT

Wildcards In SQL Server Stored Procedures

Jul 20, 2005

I thought this problem would go away over the Christmas holiday, butof course it did not. I'm trying to write a stored procedureincorporating wildcards, so I can search for variations. Example, ifname 'Smith' is submitted, sproc should retrieve all recordscontaining 'John Smith', 'Zenia Smith', 'Smithfield & Co.' You get theidea.Using SQL Query Analyzer, the queryselect * from filewhere name like '%smith%'works like a charm.But if I write a stored procedure declaring the variable @name andusing a where clause 'where name like '%@name%'', I get zero results.The query doesn't bomb. It just doesn't produce anything - even thoughI know there are records that meet the criteria.Any ideas? Or are sprocs and wildcards incompatible?

View 1 Replies View Related

Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)

Nov 1, 2007

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?
 

View 1 Replies View Related

Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?

Mar 3, 2008

Hi all,

I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):

(1) /////--spTopSixAnalytes.sql--///

USE ssmsExpressDB

GO

CREATE Procedure [dbo].[spTopSixAnalytes]

AS

SET ROWCOUNT 6

SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName

FROM LabTests

ORDER BY LabTests.Result DESC

GO


(2) /////--spTopSixAnalytesEXEC.sql--//////////////


USE ssmsExpressDB

GO
EXEC spTopSixAnalytes
GO

I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")

Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)

sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure

'Pass the name of the DataSet through the overloaded contructor

'of the DataSet class.

Dim dataSet As DataSet ("ssmsExpressDB")

sqlConnection.Open()

sqlDataAdapter.Fill(DataSet)

sqlConnection.Close()

End Sub

End Class
///////////////////////////////////////////////////////////////////////////////////////////

I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)

Please help and advise.

Thanks in advance,
Scott Chang

More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.




View 11 Replies View Related

T-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure

Nov 14, 2014

I am new to work on Sql server,

I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.

Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.

View 1 Replies View Related

SQL Server 2014 :: Embed Parameter In Name Of Stored Procedure Called From Within Another Stored Procedure?

Jan 29, 2015

I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?

CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],

[Code] ....

View 9 Replies View Related

Connect To Oracle Stored Procedure From SQL Server Stored Procedure...and Vice Versa.

Sep 19, 2006

I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.

How do I do that? Articles, code samples, etc???

View 1 Replies View Related

Grab IDENTITY From Called Stored Procedure For Use In Second Stored Procedure In ASP.NET Page

Dec 28, 2005

I have a sub that passes values from my form to my stored procedure.  The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page.  Here's where I'm stuck:    Public Sub InsertOrder()        Conn.Open()        cmd = New SqlCommand("Add_NewOrder", Conn)        cmd.CommandType = CommandType.StoredProcedure        ' pass customer info to stored proc        cmd.Parameters.Add("@FirstName", txtFName.Text)        cmd.Parameters.Add("@LastName", txtLName.Text)        cmd.Parameters.Add("@AddressLine1", txtStreet.Text)        cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue)        cmd.Parameters.Add("@Zip", intZip.Text)        cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text)        cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text)        cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text)        cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text)        cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text)        ' pass order info to stored proc        cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue)        cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue)        cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue)        'Session.Add("FirstName", txtFName.Text)        cmd.ExecuteNonQuery()        cmd = New SqlCommand("Add_EntreeItems", Conn)        cmd.CommandType = CommandType.StoredProcedure        cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc)   <-------------------------        Dim li As ListItem        Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar)        For Each li In chbxl_entrees.Items            If li.Selected Then                p.Value = li.Value                cmd.ExecuteNonQuery()            End If        Next        Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder)  and pass that to my second stored procedure (Add_EntreeItems)

View 9 Replies View Related

SQL Server 2012 :: Executing Dynamic Stored Procedure From A Stored Procedure?

Sep 26, 2014

I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure

at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT

I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT

View 3 Replies View Related

System Stored Procedure Call From Within My Database Stored Procedure

Mar 28, 2007

I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.

How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?

Thanks in advance

View 9 Replies View Related

Ad Hoc Query Vs Stored Procedure Performance Vs DTS Execution Of Stored Procedure

Jan 23, 2008



Has anyone encountered cases in which a proc executed by DTS has the following behavior:
1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio
2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio

What could explain this?

Obviously,

All three scenarios are executed against the same database and hit the exact same tables and indices.

Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).

The DTS execution effectively never finishes even after many hours (10+)
The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query)
The Update ad-hoc query will finish in 2 minutes

View 1 Replies View Related

Sql && Wildcards

Jan 22, 2007

HelloI am trying to search 2 columns on a databsae table  using a string put into a box, the code i have at the moment is SqlConnection conn =                        new SqlConnection(SqlDSFindPost.ConnectionString);        SqlCommand cmd =          new SqlCommand            ("SELECT * FROM tblBlog WHERE UserName LIKE @UserName OR Title LIKE @Title; ", conn);        cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 50).Value = '%' + TextBox1.Text + '%';        cmd.Parameters.Add("@Title",SqlDbType.NVarChar, 50).Value = '%' + TextBox1.Text + '%';        conn.Open();        cmd.ExecuteNonQuery();        GridView1.DataBind(); I have tried all sorts of strings and even typeed the string directly into the parameter but never get any results, yet when i type the wildcards directly into the textbox i get the correct rows returned. Can anybody see anything wrong with my code  and tell me where i am going wrong, or alternativly point me in the direction of some c# code for searching a database similar to the search box abovei dont do a lot in asp or c# so this is driving me crazy  Thanks for looking 

View 2 Replies View Related

USING WILDCARDS

Jan 19, 2005

Does anyone know how I could show all the records of tools with the word released after them? For example, 'Volume Monitor 4.4 Released'

I tried this statement with no luck:
Select * from Issues where Tools LIKE 'RELEASED %'

Thanks,
Russ

View 6 Replies View Related

Using Wildcards

Feb 24, 2000

I have a need to use wildcards in a sql statement. e.g. select * from tbl where field='%computer%'.
How can I substitute the string "computer" for a variable declared in the stored procedure.
Procedure Sample
@Str varchar(50)
AS
select * from tbl where field = '%' & @Str & '%'
(How do incorporate the wildcard variable @Str?

View 2 Replies View Related

Wildcards In BCP

Apr 21, 1999

I am running the data import below in a stored procedure:

EXEC xp_cmdshell "bcp TCCSTGB..TGB_Fimport in d:MSSQLTGB_ImportsNNNYYYYMMDDHHMM.SDF /f d:mssqlFormatsTGB_Fimport.fmt /Usa /P ", no_output

I want to replace the NNNYYYYMMDDHHMM with a wildcard (for example *), so that import will pull ANY .SDF files in, but it will not run. i get the following:

output
------------------------------------------------------------------------------
DB-LIBRARY error:
Bcp: Unable to open host data-file.

View 1 Replies View Related

Using Wildcards %

Nov 1, 2007

Hi

I need to replace the use of wild cards in my query with something else which achieves the same thing. The problem is the web application which uses the query does throws an error when using '%' characters. Any ideas?

The following statement appears in the where clause:

AccType.Value like '@Opened_By[%DIST%APP% as Distance and Business Provider, DIST% as Distance, APP% as Business Provider]'

Thanks

View 1 Replies View Related

User 'Unknown User' Could Not Execute Stored Procedure - Debugging Stored Procedure Using Visual Studio .net

Sep 13, 2007

Hi all,



I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.



Whenever I tried to right click stored procedure and select step into store procedure> i get following error



"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"



I am not sure what needs to be done on sql server side



We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.

Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?



Please advise..

Thank You

View 3 Replies View Related

Sql Case With Wildcards ???

Mar 28, 2007

Hi,
 
I’m trying to use case statement in my view with wildcards for '%Tradies%', instead of listing all items      
 
WHEN 'Tradies Rebate1' THEN 'Test'
WHEN 'Tradies Rebate2' THEN 'Test'
WHEN 'Tradies Rebate3' THEN 'Test'
WHEN 'Tradies Rebate4' THEN 'Test'
 
At this moment '%Tradies%' does not work and gives me null values in EventGroup column.
 
Here’s my statemnt
------------------------------------------
CASE [dbo].[Event].[EventName]
      --WHEN 'Tradies Rebate1' THEN 'Test'
      --WHEN 'Tradies Rebate2' THEN 'Test'
      --WHEN 'Tradies Rebate3' THEN 'Test'
      --WHEN 'Tradies Rebate4' THEN 'Test'
      WHEN '%Tradies%' THEN 'Test'
      WHEN 'Install Products' THEN 'All Installed'
      WHEN 'Installation Product Conversion' THEN 'All Installed'
      WHEN 'Installation Products' THEN 'All Installed'
      WHEN 'BK 3' THEN 'All Bright Kids'
END AS [EventGroup],
------------------------------------------
 
 
 
Please help!!!
 

View 4 Replies View Related

How To Use Wildcards In WHERE Clause

May 5, 2004

I have a SQL statement which is generated dynamically. I need to know what is the correct syntax for this

WHERE status = 'open' AND salesman = * AND dat = * AND customername = *

i.e. fetch everything WHERE status = 'open'

I know that simply WHERE status = 'open' would do the trick but I need it like the first example because of the way the statement is being generated i.e. this salesmen bit is like this.

If Salesman <> "*" Then
sql2 &= " AND salesman = '" & Salesman & "'"
Else
sql2 &= " AND salesman = *"
End If

Thanks

Ben

View 2 Replies View Related

Wildcards In Words

Apr 28, 2005

Hi I'm using the full-text indexing on a table and I'm trying to implement a search where users can search for words and use wildcards themselves. However I'm working on a method so that can enter a wildcard in the middle of a word to get records where they are unsure of the spelling etc.
For instance, a search of 'Ste*en' should return results like 'Steven' and 'Stephen' etc. So if they are searching for word 'establishment' they can search for 'estab*ment' and it should return all the records using this query:
SELECT * FROM myTable WHERE CONTAINS(myField,'"estab*ment"')
If I do a wildcard at the end e.g: SELECT * FROM myTable WHERE CONTAINS(myField,'"estab*"')
I get the results I am looking for. But the middle wildcard does not seem to work as expected even though it is the syntax used on MSDN and other SQL info sites.
Is there something I am not doing properly?

View 4 Replies View Related

How To Insert With Wildcards

Aug 22, 2000

hello!

it's a little stupid but i can't seem to insert a certain data.
it's like this:

insert into dept(dept_no,dept_name)
values(4,"name's")

how do i insert with the (') included in the string?

View 1 Replies View Related

Wildcards In SQL Server

Nov 1, 1999

I have a stored procdure in SQL Sever that accepts paramteres. I am trying
to return rows where parameter that is passed is somewhere in the cuustomer's
name. Without the variable the SQL would look like this:

SELECT * FROM tbl
WHERE CustomerName LIKE '%Smith%'

I can't figure out how to replace LIKE '%Smith%' with a varible. I tried
'%@CustomerName%', ('%' + @CustomerName + '%') and neither works. Any ideas?
Thanks

ps my column's type is char(50) and so is the variable so trailing spaces
don't matter.

View 1 Replies View Related

Problems With Wildcards

Sep 7, 2007

Hi all,I am creating an ASP.NET site, and I'm having lots of issues trying to get wildcards to work with the following query:DECLARE @Status varcharDECLARE @AssignedTo intDECLARE @AppID intSELECT dbo.Issue.IssueID, dbo.Issue.ReportedBy, dbo.Issue.ShortDescription, dbo.Issue.DateReported, dbo.Issue.Status, dbo.Priority.Description AS Priority, dbo.Application.ApplicationFROM dbo.Issue INNER JOIN dbo.Priority ON dbo.Issue.Priority = dbo.Priority.PriorityCode INNER JOIN dbo.Application ON dbo.Issue.Application = dbo.Application.ApplicationIDWHERE (dbo.Issue.Status LIKE '%' + @Status) AND (dbo.Issue.AssignedTo = @AssignedTo) AND (dbo.Application.ApplicationID LIKE '%' + @AppID)ORDER BY dbo.Priority.PriorityCode When running this through query analyser I get the error:Server: Msg 245, Level 16, State 1, Line 5Syntax error converting the varchar value '%' to a column of data type int. Could someone help me understand this please?Thanks

View 5 Replies View Related

Wildcards And Numbers

Apr 18, 2007

How do Iput wildcards around a number in an sp ? If my user leaves BoxNo blank it will list all boxes


SELECT *
FROM tblFiles
WHERE
ConNo =@strRMUConsignmentNo
and FileRef like '%'+@strtxtFileRef+'%'
and Subject like '%'+@strtxtSubject+'%'
and FileDescription like '%'+@strtxtDescription+'%'
and BoxNo like %+@strBoxNo%

View 14 Replies View Related

Wildcards On Columns?

Nov 29, 2005

My use of wildcards thus far has been limited to matching a givenstring anywhere in a column as follows:SELECT * FROM Table WHERE Column LIKE '%string%'However, I'm wondering if there's a way to do this in reverse. Thatis, is there a way to match the column anywhere in the string?Pseudo-coding it as:SELECT * FROM Table WHERE 'string' LIKE %Column%What I'm trying to match is network addresses. Most of the storedaddresses in this table are exact (i.e. ip-1-2-3-4.location.isp.com)but sometimes they encompass an entire group (i.e. location.isp.com).When an exact address is given in the code I'm writing, it needs tomatch any rows that contain its exact self or contain a shortenedversion of which it is part.Any ideas?-cyber0nehttp://www.cyber0ne.com

View 3 Replies View Related

Findstring And Wildcards

Nov 29, 2007

Can you use wildcards with findstring? The documentation does not address this.

So far I haven't had any luck.

View 3 Replies View Related

Using Wildcards Efficiently With Equals Or LIKE

Jul 6, 2006

Is it possible to use wildcards with an equals statement? Such asSELECT * FROM Table WHERE City = '%' AND State='Ca'Bascially just stating where city equals anything...I know you can do it with a LIKE statement such as...SELECT * FROM Table WHERE City LIKE '%' AND State='Ca'but is that very efficient?The reason I want to do this is because I want to programmitcally set the city, so just ommiting it won't work
Also, using City LIKE '%' seems to not include NULL...is there anywayto include NULL as well as anything else?
Thanks for your help!

View 2 Replies View Related

Is There Such A Thing As Table Name Wildcards?

Jan 8, 2007

Hey all,I have a datagrid with populated by this query: SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE (TABLE_TYPE = 'BASE TABLE')I have paging, sorting and selection enabled.Now I am looking for a way to use a wild card as a placeholder for the table name in my select statements so I can use the valued selected from the datagrid.Example : SELECT * FROM %TABLENAME%TIAWOOHOO! my first post. 

View 2 Replies View Related

Dropping Constraints Using Wildcards?

Jun 1, 2004

Hi,

Is it possible to use wildards in SQL to drop a constraint on a table?

Thanks!

View 4 Replies View Related

Using Wildcards To Select Fields

Jun 23, 2008

I have a really large table with many Proposal fields and corresponding approval fields.

Propose1
Approve1
Propose2
Approve2

Would it be good practice or even possible for me to select all proposal fields using a wildcard somehow within the select statement. If it is ok, how would I go about doing it?

View 5 Replies View Related

Wildcards In Dropping Constarints

Jul 20, 2005

Hi,Is it possible to use wildards in SQL to drop a constraint on a table?Thanks!

View 3 Replies View Related

INDEX Possible For Reverse Wildcards?

Jul 20, 2005

If I use _reverse_ wildcard search will it always result in a tablescan? Is it possible to get the DB (Oracle or SQL server) to useindexes when doing reverse wildcard match?let's say I have:table email_address (id int, email varchar)with the following entries2, www.%shoes.%3, w%.super%shoes.%4, %webbox.somecopany.comselect id from email_address where 'www.superdupershoes.com' likeemail;this returns 2,3But the query always results in a table scan even if I add an index toemail. What kind of index can I employ in this situation?Please note that this is a _reverse_ search, the opposite of what'snormally done, i.e. select from email_address where email like'www.%shoes.com'.Thanks!- Robert

View 9 Replies View Related

Multivalue Parameters And Wildcards

Dec 14, 2007



Greetings all
trying to get a multivalue parameter to accept either typed in data ex: 111111111,111111112 or if I want to return all id numbers type in %. Problem is when I test it by typing in 111111111,111111112 it throws an error saying " Incorrect syntax near ','.



I can enter 111111111 or % and get results, the error comes when I try to type in two or more id numbers. the parameter in the dataset looks like

where a.id_number LIKE (@id_number+ '%')


any suggesstions? Im sure there are threads out there but Im pressed to meet a deadline and wanted to see if there were any quick solutions

thanks
km

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved