.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Using SELECT with LIKE % surrounding a variable

Posted By:      Posted Date: September 08, 2010    Points: 0   Category :Sql Server
Hi  Can anyone give me some basic help please. I'm just trying to learn to write to SQL to make my job easier. I've started using variables but the SELECT statement does not work properly when I use it with LIKE and try to surround my variable with % wildcards. Can anyone tell me the correct syntax please :-   DECLARE @location varchar SET @location='L'   SELECT DESCRIPTION,ID,* FROM LOCATION WHERE UPPER(DESCRIPTION) LIKE ('%' + @location + '%')  Many thanks Matthew

View Complete Post

More Related Resource Links

How To Map A GUID Variable And Use It In An OLE DB Source Data Flow Select Query


I have a simple SSIS package that does the following. 

On the control flow tab, there is an Execute SQL task that calls a stored procedure and returns a GUID. 

I have a package scoped variable that stores a UNIQUEIDENTIFIER output from the stored procedure. It appears that the package variable data type does not include a GUID data type so I tried to use string instead.

Next, the Execute SQL task is mapped to a data flow task. 
Within that data flow task I have an OLE DB Source which connects to a SQL Server Destination task.

On the OLE DB Source Editor screen I have a query that performs the following:

SELECT ID, SessionKey, ....
FROM dba_DroppedLoginsLog
WHERE SessionKey = ?

I have clicked on the parameters button to map the Parameter to my variable called User::SessionKey. This is the output variable from my Execute SQL Task component on the Control Flow tab. This value should be a GUID, but again, I don't see a data type for that.

When I execute the package it fails on the OLE DB Source with the following error:
[OLE DB Source [1908]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE D

Select only works with datetime variable, not table



I am a learning-as-I-go person without formal training.  I have been working with Access databases for about 10 years, and have begun working with SQL in the last 5 years or so.  The system in user here is a 2K3 server (8xXeon 2.8Ghz, 12GB RAM, 1TB HDD) running SQL Server 2005 (32bit).  I am trying to develop a SQL query (view) which looks for values of one table' field at specific times based on a table of those times.  When I use a datetime variable, that particular value at that particular datetime will be returned.  When I try to replace the variable with the datetime result of a second query(view), no data is returned.  The code is below.

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = '2010-09-30 04:59:00'		-- Need to replace this value with the the list of 'PriVolCk' datetimes

Select Top @variable


I want to do something like this: SELECT TOP @variable.
I cannot use stored procedure for this report, 'cause I do not have access to prod db, I'm just sending rdl for upload. How to make it work on dataset level or through the report layout?

I know SQLServer doesn't recognize "Select Top @variable" statement. 
SELECT    top &variable, Ticket.problem AS Issue, COUNT(Solutions.solution_date) AS [Tickets Touched]
FROM         Ticket INNER JOIN
                      Solutions ON Ticket.ticket_number = Solutions.ticket_number
WHERE     GROUP BY Ticket.problem


SELECT     Ticket.problem AS Issue, COUNT(Solutions.solution_date) AS [Tickets Touched]
FROM         Ticket INNER JOIN
                      Solutions ON Ticket.ticket_number = Solutions.ticket_number
WHERE     GROUP BY Ticket.problem
HAVING      (COUNT(Solutions.solution_date) > CAST(@num AS int))

OLE DB Destination SQL command - how can I select from recordset variable?


How do I insert records from connection1 into a a table with the same name on connection2 where the records in connection2 do not already exist?

The approach I'm trying is this: I have an OLE DB Source that selects records from connection1 (successfully). The flow into a recordset destination in the Data Flow. But I can't figure out how to select from this recordset using the FROM CLAUSE of a SQL Command in the OLE DB Destination component. Can someone point me to an example of this?

It seems like this would be a common task but I can't find any examples.

SQL Select With Variable Table - Using with ODBC Data Source


Hello all,

Having difficulty with an idea for a SQL query today.  I am working with a data set that points to an ODBC data source.  The data source has several TRANSACTIONmmyyyy tables (mm - month, yyyy - year ), each holding data for a particular month, going back several years. Each table has identical fields and data types.

If I were to run the query below for just one month ( say I took out mmyyyy and plugged in a month and year, 052010 ) it will pull totals for the AccountNum I specify.


mmyyyy.AcctNum, SUM
mmyyyy.Amount ) As
 "Current Month"

TRANSACTION mmyyyy.Region = TOTALS.Region AND
TRANSACTION mmyyyy.Region

select record where field = variable?


Hi, I'm very new to SQL so please excuse how simple this problem is:

I've got a web page which contains a form, the form holds the following boxes:

  • Latitude
  • Longitude
  • Accuracy
  • Update
  • UserName

I have also got an access database , containing one table (Table1) which has the fields:

  • Latitude
  • Longitude
  • Accuracy
  • Update
  • UserName

The "UserName" field is the primary key (containing an email address)

What I want to do is select the record that has the same email address as entered into the form and update the rest of the fields with the data entered into the form.

The problem I'm having, as I see it, is that I can't seem to compare a string to a record UserName?

When I enter the email address manually it works and updates the record but it won't pull the email address from the variable string?

Here is my .asp code:


'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsUpdateEntry 'Holds the recordset for the record to be updated
Dim strSQL 'Holds the SQL query for the database
Dim email 'Holds the current users email address

email = (Request.Form("UserName"))

Set dbConn = Server.CreateObject(

problem with SQL select statement when variable are NULL


Hello all,

I'm struggling with a strange problem.

Let me first start with giving you my SQLdatasource:


      <asp:SqlDataSource ID="SqlDataSource3" runat="server" 
        ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
        SelectCommand="SELECT ID_PLS, Checked, Userid, DONE, Entity, Environment, TO_DO, Description, HOW_TO, LINK_TO_PROC, Overtime, Processing_date, Driver FROM PLS_DAILY WHERE (Entity = @Entity) AND (Environment = @Environment) OR (Entity = @Entity2) AND (Environment = @Environment2)">
               <asp:SessionParameter DefaultValue="" Name="Entity" SessionField="eses_entity" 
                   Type="String" />
               <asp:SessionParameter DefaultValue="" Name="Entity2" SessionField="eb_entity" 
                   Type="String" />
               <asp:SessionParameter DefaultValue="" Name="Environment2" SessionField="eb_environment" 
                   Type="String" />
               <asp:SessionParameter DefaultValue="" Name="Environment" SessionField="eses_environment" 
                   Type="String" />

Then let me give you the sessionfield parameters:

    Protected Sub Button1_Click(ByVa

AJAX GridView Checkbox Select All in ASP.Net

In the previous article GridView Select Multiple Rows you learnt the multiple row selection in GridView control. Here you will get the C# code sample to select all the gridview rows using single checkbox server control. You can highlight the selected rows with different color and Font state Bold. If you want to use the selection values on different ASP.Net

Local variable scoping in C#

In my previous post, Compiler-generated scopes for local variable declarations, I briefly touched on the issue of multiple meanings applied to the same name. In this post, I'll aim to flush out the compiler's rules with regards to binding names in their local scopes.

Compiler-generated scopes for local variable declarations

I was tasked with understanding and fixing a bug on error reporting with foreach iteration variables the other day, and it got me thinking about local variable scoping rules in C# in general. First, the bug.

Retrieving a variable from a previous page



I'm looking to have a search page where a user will select an item from a listview, then then taken to another page that wil display data based on their original selection.

A user will type in a string and be presented with a list of usernames that are like that string, when they select a user name who's profile they would like to view I would like the page that they're taken to, to be that users profile.

How would I transfer the variable from the search page to the profile page, and when I work on the profile page, how can I access that variable?


Embeb a variable number of videos


I need to provide some alternate content for my non-Flash users.

I have a directory of video files that is scanned using the DirectoryInfo() function, and I want to use the results to embed all files ending in ".mov" in QuickTime players on a web form.

How would you approach this? Right now I am using the count on the array of videos to determine how many times to repeat the embed code.  But this seems inelegant.

Is there a way to have a single QuickTime player.ascx control that gets duplicated according to the number of videos available for display?

How to take value of bound field of grid in a variable or in a textbox.


How to take value of bound field of grid in a variable or in a textbox.

 I m using this code in .aspx page for grid.

<asp:GridView ID="Grd_Mprocess" runat="server" AutoGenerateColumns="False"

                      Width="100%" AllowPaging="True"


                      AllowSorting="True" PageSize="5"  >

                 <RowStyle CssClass="odd" />


<asp:BoundField HeaderText="DOC_TYPE_PK" DataField="DOC_TYPE_PK"  Visible="true" >


  <asp:TemplateField HeaderText="DOC TYPE ID" Visible="true">  &nbs

Select value in gridview


Hi, I am new to gridview. I want to make a gridview. Its row when selected should direct me to a specific .aspx page.Each row should direct to a different page. Should I use querystrings for this? Please suggest. Regards, ap.

Hidden variable has no value


I know I have a valid solution, but the js is telling me that the value in the hdnRecordSaved variable has no value!
Even when I specify in my function the following, it still tells me it has no value:
var myTextField = document.getElementById('hdnRecordSaved');

When I put the full value of the variable in (ctl00_ctl00_cphTopCenter_cphCenter_hdnRecordSaved.value), I get the correct contents back ("1"), which I set in my code behind. However, I would prefer to just put the short name in since the masterpages may change, etc. Unfortunately, I know I can get around this with VS2010, but we are using VS2008.

What am I doing wrong with the js naming conventions that can't give me the contents? Here is my below js.

<asp:Panel ID="pnlMapFields" runat="server" ScrollBars="Both" Height="430px">

<telerik:RadTreeView ID="RadTreeView1" runat="server" DataTextField="Description"

DataFieldParentID="MapFieldparentID" DataF

How to retrieve data from SqlDataSource and pass to a variable?


Hi there,

I have a page in my website where there is very little code-behind. I only have two lines of code in Page_Load() and the rest of the code is declarative ASP.NET code. I have a FormView control which uses a SqlDataSource to get some order information. The SqlDataSource calls a stored procedure called usp_GetOrderDetails, here's the code for the SqlDataSource:

    <asp:SqlDataSource ID="sdsFormOrderDetails" runat="server" 
ConnectionString="<%$ ConnectionStrings:ForexDB %>"
ProviderName="<%$ ConnectionStrings:ForexDB.ProviderName %>"
SelectCommand="usp_GetOrderDetails" SelectCommandType="StoredProcedure"
UpdateCommand="usp_UpdateOrder" UpdateCommandType="StoredProcedure"
<asp:ControlParameter Name="orderId" ControlID="grdOrders" PropertyName="SelectedDataKey.Value" />
<asp:ControlParameter Name="orderId" ControlID="frmOrderDetails" PropertyName="SelectedValue" />
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend