sql 2008 Simple Dynamic query

HI all

I am run this simple query on sql 2008  and getting erro

declare @sql as varchar(50)

select @sql='select order_trn.fac_order,order_Trn.article_no from order_trn 
where order_trn.fac_order like 'N-5%''

EXEC(@sql)


Please help
thanks

0
Capri
4/13/2010 11:49:18 AM
sqlserver.programming 1873 articles. 0 followers. Follow

4 Replies
705 Views

Similar Articles

[PageSpeed] 43

Hmmm, do you really need dynamic?
select @sql='select order_trn.fac_order,order_Trn.article_no from order_trn

where order_trn.fac_order like ''N-5%'''

See if the above works, I hope   you are aware of SQL Injection....





"Capri" <NoEmail@NoDomain.com> wrote in message 
news:B7906BDD-68F6-484C-9582-C2FC854F44E2@microsoft.com...
> HI all
>
> I am run this simple query on sql 2008  and getting erro
>
> declare @sql as varchar(50)
>
> select @sql='select order_trn.fac_order,order_Trn.article_no from 
> order_trn where order_trn.fac_order like 'N-5%''
>
> EXEC(@sql)
>
>
> Please help
> thanks
> 


0
Uri
4/13/2010 12:01:51 PM
> select @sql='select order_trn.fac_order,order_Trn.article_no from 
> order_trn where order_trn.fac_order like 'N-5%''

You need to double-up single quotes within the string:

select @sql='select order_trn.fac_order,order_Trn.article_no from 
dbo.order_trn
where order_trn.fac_order like ''N-5%'''

A better method is specify a parameterized query and execute with 
sp_executesql:

DECLARE
	@sql nvarchar(max),
	@fac_order varchar(10);

SELECT @sql=N'select order_trn.fac_order,order_Trn.article_no from 
dbo.order_trn
where order_trn.fac_order like @fac_order';

SET @fac_order = 'N-5%';

EXEC sp_executesql @sql,
	N'@fac_order varchar(10)',
	@fac_order = @fac_order;
GO

However, I see no need to use a dynamic SQL Statement at all here; a simple 
parameterized query should do the job:

DECLARE
	@sql nvarchar(max),
	@fac_order varchar(10);

SET @fac_order = 'N-5%';

SELECT
    order_trn.fac_order,
    order_Trn.article_no
FROM dbo.order_trn
WHERE order_trn.fac_order LIKE @fac_order;

See Erland's article on dynamic SQL at 
http://www.sommarskog.se/dynamic_sql.html for details.

-- 
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Capri" <NoEmail@NoDomain.com> wrote in message 
news:B7906BDD-68F6-484C-9582-C2FC854F44E2@microsoft.com...
> HI all
>
> I am run this simple query on sql 2008  and getting erro
>
> declare @sql as varchar(50)
>
> select @sql='select order_trn.fac_order,order_Trn.article_no from 
> order_trn where order_trn.fac_order like 'N-5%''
>
> EXEC(@sql)
>
>
> Please help
> thanks
> 
0
Dan
4/13/2010 12:06:52 PM
> I am run this simple query on sql 2008  and getting erro

Next time, tell us the exact and complete error message that you are 
receiving.


0
Scott
4/13/2010 12:16:50 PM
Thank you very much,


"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message 
news:ABCF440B-9C4F-4B69-A6AF-9B76312DC1BB@microsoft.com...
>> select @sql='select order_trn.fac_order,order_Trn.article_no from 
>> order_trn where order_trn.fac_order like 'N-5%''
>
> You need to double-up single quotes within the string:
>
> select @sql='select order_trn.fac_order,order_Trn.article_no from 
> dbo.order_trn
> where order_trn.fac_order like ''N-5%'''
>
> A better method is specify a parameterized query and execute with 
> sp_executesql:
>
> DECLARE
> @sql nvarchar(max),
> @fac_order varchar(10);
>
> SELECT @sql=N'select order_trn.fac_order,order_Trn.article_no from 
> dbo.order_trn
> where order_trn.fac_order like @fac_order';
>
> SET @fac_order = 'N-5%';
>
> EXEC sp_executesql @sql,
> N'@fac_order varchar(10)',
> @fac_order = @fac_order;
> GO
>
> However, I see no need to use a dynamic SQL Statement at all here; a 
> simple parameterized query should do the job:
>
> DECLARE
> @sql nvarchar(max),
> @fac_order varchar(10);
>
> SET @fac_order = 'N-5%';
>
> SELECT
>    order_trn.fac_order,
>    order_Trn.article_no
> FROM dbo.order_trn
> WHERE order_trn.fac_order LIKE @fac_order;
>
> See Erland's article on dynamic SQL at 
> http://www.sommarskog.se/dynamic_sql.html for details.
>
> -- 
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
> "Capri" <NoEmail@NoDomain.com> wrote in message 
> news:B7906BDD-68F6-484C-9582-C2FC854F44E2@microsoft.com...
>> HI all
>>
>> I am run this simple query on sql 2008  and getting erro
>>
>> declare @sql as varchar(50)
>>
>> select @sql='select order_trn.fac_order,order_Trn.article_no from 
>> order_trn where order_trn.fac_order like 'N-5%''
>>
>> EXEC(@sql)
>>
>>
>> Please help
>> thanks
>> 

0
Capri
4/15/2010 7:04:25 AM
Reply:

Similar Artilces:

Use Outlook to send to contacts in a query
Is there a way to set Outlook to send to contacts stored in a query (or a table) WITHOUT just importing them and doing it that way? I want to create a database, set Outlook to look in it, and have it send to contacts whose contact information will be listed in that database. Alan: Yes, there is a way. But have you explored, and rejected, storing contacts in Outlook and creating Outlook Distribution Lists? If you really need to store your contacts in Access, then you could use VBA code in your Access database to start Outlook and send and an email (or Mailitem as Outlook calls it). Y...

Entourage 2008
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: Exchange I want to point out that the question above: &quot;What type of email client are you using?&quot; doesn't offer a complete choice. I use Entourage to pick up my mail for three different email accounts - from an Exchange server; an IMAP server and; a POP2 server. <br><br>I first posted this problem a couple of months ago and was told that it was a WebCore issue and there was nothing to do, except for a work around, until a fix came out. <br><br>I think M...

Money Plus Deluxe 2008 Spending Tracker slowdown issue...
I've narrowed down a few things on Money Plus Deluxe 2008.... 1a. The slowness of start-up comes from having the Spending Tracker placed on the home screen. Each time you click to another area-register/bills/Budget/etc-and click back to home, it takes it longer and longer to get back and refresh the home screen each time you navigate away and come back. 1b. What have I done to determine this? a. I have turned off the MSN syncing, turned off the sponsorship link, removed the Passport Features and the password on the file with no gain of speed. b. I did a Standard Fil...

One Dynamics server with multiple databases on 2 servers?
What licensing is needed to run with the Company databases on two or more different servers. We are happy just to have to one Dynamics database - do all compnay databases have to live on the same server. Thanks The dynamics and company databases must be one one server. -- Charles Allen, MVP "mfhau" wrote: > What licensing is needed to run with the Company databases on two or more > different servers. > We are happy just to have to one Dynamics database - do all compnay > databases have to live on the same server. > > Thanks so what's the story wi...

Re: Formula in MS Query #2
Thank you for your help. MS Query results in error "unrecognized function name in MS Query", when I use INT(Filename.Time) In MS Query, use the expression: INT(Filename.Time) This will return all dates with time set to zero. Krish wrote: > Unfortunately the format command wiill not allow grouping of the dates, > since time is appended with date in every field. > > You could import the Time field in its Date/Time format, then reformat > the column in Excel > (Format>Cells, Number tab -- Date category) > > Krish wrote: > >>I am trying to import...

Stylus Studio 2008 Release 2: New EDI support & code generation
[Announce] Dear microsoft.public.dotnet.xml, Stylus Studio 2008 Release 2 is now available.The following highlights some of the new features included in our award-winning XML tools: * Convert New EDI Formats to XML: Stylus Studio now supports the Edig@s EDI dialect. With a new Edig@s to XML Schema wizard, as well as support for the IATA 07.1 revision and numerous other formats including EANCOM, X12, and HL7, the Stylus Studio product's EDI to XML conversion tools are among the most comprehensive available today. * Java and C# for .NET Code Generation: Code generation has numerous improve...

Parameter Query 10-04-07
I have a query which prompts for a parameter value. I want to use the value twice in the same expression. How can I do this without repeating the parameter prompt. Any help welcome. J ...

Query Using Time
My table name is Master. I would like to create a query that uses the field [Login] from the Master table. I would like for the query to display any entry that comes after 8:05:59 AM. I have tried using >#8:05:59 AM#, but this does not work. Please offer any suggestions. Thanks. Nevermind...fixed the issue. Thanks. "Chris" wrote: > My table name is Master. I would like to create a query that uses the field > [Login] from the Master table. I would like for the query to display any > entry that comes after 8:05:59 AM. I have tried using >#8...

A simple question requiring a simple answer
When typing in a cell how do I get the text to drop down a line yet stay in the same cell? ie. In word you hit enter and the cursor drops to the next line. In excel you hit enter and the next cell is selected. -- Pedros ------------------------------------------------------------------------ Pedros's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28202 View this thread: http://www.excelforum.com/showthread.php?threadid=562306 Pedros, use Alt+enter to get a new line in the same cell -- Paul B Always backup your data before trying something new Please post...

Which server should be the best
I have two new servers that both meet the minimal specs. One is faster and has more memory. Use it for SQL Server or the CRM Server? What about hard drive space? In general, I'd make the SQL box teh better one (especially with memory). Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On Wed, 10 Nov 2004 13:50:05 -0800, "Larry" <Larry@discussions.microsoft.com> wrote: I have two new servers that both meet the minimal specs. One is faster and has more memory. Use it for SQL Server or the CRM Server?...

Date activated query!
If my tblHorseInfo has these fields HorseID.Number, DateAlert.Date/Time , AlertInfo.Text How would I design the query to show this on the Date Set I am going to have a Listbox [ListAlert]on my Main Menu that will be visible when there is a record to Show Me.ListAlert.Visible = IIf(Me.ListAlert.ListCount = 0, False, True) showing these 3 fields -- Thanks in advance for any help with this......Bob MS Access 2007 accdb Windows XP Home Edition Ver 5.1 Service Pack 3 "Bob Vance" <rjvance@ihug.co.nz> wrote in message news:%23tXdL30rKHA.5116@TK2MSFTNGP04.phx...

Query left outer join
I have the following query that I wish to return ALL vehicles and then show all trips applicable to those vehicles for the current date (pulled from a form). The problem I'm having is that the query returns only the vehicles that have trips for the day, not ALL vehicles. SELECT vehicle.vehicle_label, trips.trip_from, trips.trip_to, trips.pickup_date, trips.drop_date, trips.closed, trips.timeField, vehicle.SubContractor FROM vehicle LEFT JOIN trips ON vehicle.vehicle_label = trips.vehicle_fkey WHERE (((trips.pickup_date)=[Forms]![frmAllocation]![AllocationDateField]) AND (...

How to add a system data source for sql server 2005 express in ODBC
Hi, I add a system data source in ODBC administraor for local sql server 2005 database successfully. But I could not do the same thing for my local sql server 2005 express. For the server box, I tried [local] and sqlexpress, deselect Connect to sql server to obtain default settings for the additional configuration options. Attach database filename: I enter in the file path like: c:\test\test.mdf, Change the default database to: I tried to input same as the Attach database filename or just leave it as blank. But finally it cannot connect successfully. I am using windows xp p...

Combo Box Query Not Working
I’m new to access but learning quickly. I’m trying to create a data base for our “time change” items on out plant equipment. Such as; Furnace (asset) Clutch (part) Installed date (date installed) Replacement date (set date in the future e.g. 6 months from install date) I already have a table with the columns named asset, part, installed date and replacement date. It’s called Table 1. All dates are formatted to medium date format and input mask the same. I also have a table with IntervalTitle, Interval and IntervalIncrement set as follows: IntervalTitle Inte...

Need SQL Assistance
I've been able to determine that our Store Ops Item database has 31 items that are INACTIVE when compared to our HQ database (I ran a SELECT COUNT(*) FROM ITEM WHERE INACTIVE = 1). However, I've been unable to determine WHICH 31 items. I'd like to run a QUERY that will show me the items in our HQ Item database that are different from the items in our Store Ops Item database. HQ is on server1 and Store Ops is on server2. Can someone familiar with SQL tell me the query? Scott Hi Luminox, You can run the following query to get the result set (can be run any server) but make ...

Query formatting problem
I have a MS access database that keeps track of road segments for my work. The road segments are stored in a table (along with another table that stores project information) and I have a query that calculates length by subtracting the beginning MP from the end MP. The query is fine for the most part except for a few values that are not being calculated correctly. Here is the code: SELECT PROJECTS.Number_ID AS PROJECTS_Number_ID, PROJECTS.OO_NUM AS PROJECTS_OO_NUM, PROJECTS.PROJECT, PROJECTS.CO_NAME, PROJECTS.CO_NUM, PROJECTS.DISTRICT, PROJECTS.Date, PROJECTS.COMMENT, ROUTE_...

PowerPoint sound files not playing back from CD Office mac 2008
I'm trying to help my daughter with her Power Point presentation. We have attached mp3 sound files to her slides. When I burn the PowerPoint file to a disk and take to a second computer to check that it will play, the sound does not play. As my computer is a desktop I can't just let her take it to school. I tried to increase under the general preferences the sound file size so that it would not try to link but that doesn't seem to work. how can I embed the sound before I save and burn the Power point presentation to a CD? So that when my daughter tabs through the prese...

Boost SQL Priority
Hello: Please tell me if the following settings are good or bad in SQL 2000/2005 for GP 9.0/10.0: Boost SQL Priority for Windows Changing Min/Max Memory Settings "Programs" instead of "Background Services/System Cache" in Performance tab of the Server's System settings Thank you, for your time! childofthe1980s Hi, I know this is an older article, but I think it should help. Microsoft SQL Server Recommended Database Settings and Supported Sort Orders with Great Plains KB 865882 I have another document that is rather large called Optimize Performance and Tro...

Replication and the 2 SQL Jobs "REPL-Snapshot"
Replication and the 2 SQL Jobs "REPL-Snapshot". We have disabled the jobs for 6 months. >From research my understanding is that these jobs just need to be run once to create the files in the mscrm/unc....folders. Once those files are created this job may be turned off. If any schema changes are made these would then need to be run again so that changes get reflected on the clients MSDE. I am after confirmation that the above is indeed correct The Sync is also very slow . Any advises ...

Cannot add contacts to Distribution Group in SBS 2008 Console
Hello, I cannot for the life of me figure out a way to add contacts to a mail enabled distribution group in SBS Console. I can add users but not contacts. My group was created just fine, I added domain users and cannot see any contacts to add. Only users, distribution groups and security groups. So I decided to head over to AD Users and Computers (I did not use that tool to create the group and I know not to go there to create groups if I want to see them in SBS Console). From there I clicked on my distribution group and saw my domain users. So then I added my mail enabled contacts ...

Creating query from other queries- dates column
I'm trying to create a query from other queries, all of which have a 'date' column. However not all the queries have the same dates in e.g one query may only have entries for 01/01/2006 and then for 05/01/2006 but not for any dates inbetween these dates, although other queries may have. But the new query seems to ignore the fact that other queries have data for these dates inbetween and only display data for the dates that appear in the first query. I want ALL dates that have data entries to appear, even if some columns will have no entries (as some queries have no data f...

Query in Access 2007
I have linked via ODBC tables from another database. I have created a query to pull in two columns of information from the two tables in the query. I would like to add an extra column that is only created when the query is run, to dynamically number each row incrementally, depending on how many rows the query returns. If it returns 10 rows, then the first column should be numbered 1 to 10, if it returns 357 rows, then the rows should automatically incrementally number each from from 1 to 357. Try this but substitute your table and field names. Prim is your primary key. This can o...

upgrading to sql 2005 04-28-06
Hi; I've got a very stable CRM3.0 deployment, running on Win2000 server and SQL2000. Because I just can't be happy with that, I was thinking of upgrading SQL to 2005. Anyone tried it? Thanks Dave Iv'e tried it with success. Take a backup of the databases and hit setup. It worked for me... -- Best regards Casper Jensen CRM Extensions www.crmextensions.com "Dave Ireland" wrote: > Hi; > > I've got a very stable CRM3.0 deployment, running on Win2000 server and > SQL2000. Because I just can't be happy with that, I was thinking of &...

A minor issue with email addressing when creating a new user in SBS 2008
I've been on SBS 2008 for a while now and have had this issue since I first migrated over. I believe I know what happened but until now have not really tried to resolve the issue. Here's the problem. When I create a new user it of course creates it's Exchange account. Problem is their email address appears as user@gocompany.com. gocompany.com is my FQDN that I use for several external access points. It is also my registered SSL certificate from GoDaddy. My work around has been after the account is created I go in and add another SMTP address for user@company.com and ...

problem with crosstab query
please i need help i have a report must be done like this list of projects p1-p2-p3-p4-p5......etc names yasser 8% - 0% - 4% - 3%- .........ect hany 2% - 1% - 1% - 0% - etc nour 0% - 5% - 0%- 20% -------etc etc what i need is to do this with crosstab query i have two tables which the data is taken from table 1 is named : emp which inclode the departments for employees and the names and idnumbers of the employees table 2 is named : Employee Weekly Hours which store all the tasks and hours for all...