Generating Sql String For Execution

Sep 6, 2004

I've a need to generate a condition clause (if statement) from 3 variables: a value, an operator & another value.

To generate this: 'abcd' like 'a%'
& verify if the condition is satisfied I've done the following:

CREATE TABLE #Temp(Result varchar(10))
declare @cond1 as varchar(50), @op as varchar(4), @cond2 as varchar(50),
@expr as varchar(50),@result as varchar(10)
set @cond1 = '''abcd'''
set @op = 'like'
set @cond2 = '''a%'''
set @expr = @cond1 + ' ' + @op + ' ' + @cond2
insert into #temp exec ('select case when ' + @expr + ' then ''true'' else ''false'' end')
if exists (select result from #temp where result = 'true')
select 'it is true'
else
select 'it is false'
drop table #temp


It works, but a bit clumsy. Is there a more elegant way to do this ?

View 3 Replies


ADVERTISEMENT

Static Variables In A SQLCLR Stored Proc Seem To Get Reused From Execution To Execution Of The Sp

Aug 23, 2007

after moving off VS debugger and into management studio to exercise our SQLCLR sp, we notice that the 2nd execution gets an error suggesting that our static SqlCommand object is getting reused from the 1st execution (of the sp under mgt studio). If this is expected behavior, we have no problem limiting our statics to only completely reusable objects but would first like to know if this is expected? Is the fact that debugger doesnt show this behavior also expected?

View 4 Replies View Related

Execution Plans &<&> Proportionate Execution Times

Dec 7, 2005

Hi I am slowly getting to grips with SQL Server. As a part of this, I have been attempting to work on producing more efficient queries. This post is regarding what appears to be a discrepancy between the SQL Server execution plan and the actual time taken by a query to run. My brief is to produce an attendance system for an education establishment (I presume you know I'm not an A-Level student completing a project :p ). Circa 1.5m rows per annum, testing with ~3m rows currently. College_Year could strictly be inferred from the AttDateTime however it is included as a field because it a part of just about every PK this table is ever likely to be linked to. Indexes are not fully optimised yet. Table:CREATE TABLE [dbo].[AttendanceDets] ([College_Year] [smallint] NOT NULL ,[Group_Code] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Student_ID] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Session_Date] [datetime] NOT NULL ,[Start_Time] [datetime] NOT NULL ,[Att_Code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY]GO CREATE CLUSTERED INDEX [IX_AltPK_Clust_AttendanceDets] ON [dbo].[AttendanceDets]([College_Year], [Group_Code], [Student_ID], [Session_Date], [Att_Code]) ON [PRIMARY]GO CREATE INDEX [All] ON [dbo].[AttendanceDets]([College_Year], [Group_Code], [Student_ID], [Session_Date], [Start_Time], [Att_Code]) ON [PRIMARY]GO CREATE INDEX [IX_AttendanceDets] ON [dbo].[AttendanceDets]([Att_Code]) ON [PRIMARY]GOALL inserts are via an overnight sproc - data comes from a third party system. Group_Code is 12 chars (no more no less), student_ID 8 chars (no more no less). I have created a simple sproc. I am using this as a benchmark against which I am testing my options. I appreciate that this sproc is an inefficient jack of all trades - it has been designed as such so I can compare its performance to more specific sprocs and possibly some dynamic SQL. Sproc:CREATE PROCEDURE [dbo].[CAMsp_Att] @College_Year AS SmallInt,@Student_ID AS VarChar(8) = '________', @Group_Code AS VarChar(12) = '____________', @Start_Date AS DateTime = '1950/01/01', @End_Date as DateTime = '2020/01/01', @Att_Code AS VarChar(1) = '_' AS IF @Start_Date = '1950/01/01'SET @Start_Date = CAST(CAST(@College_Year AS Char(4)) + '/08/31' AS DateTime) IF @End_Date = '2020/01/01'SET @End_Date = CAST(CAST(@College_Year +1 AS Char(4)) + '/07/31' AS DateTime) SELECT College_Year, Group_Code, Student_ID, Session_Date, Start_Time, Att_Code FROM dbo.AttendanceDets WHERE College_Year = @College_YearAND Group_Code LIKE @Group_CodeAND Student_ID LIKE @Student_IDAND Session_Date <= @End_DateAND Session_Date >=@Start_DateAND Att_Code LIKE @Att_CodeGOMy confusion lies with running the below script with Show Execution Plan:--SET SHOWPLAN_TEXT ON--Go DECLARE @Time as DateTime Set @Time = GetDate() select College_Year, group_code, Student_ID, Session_Date, Start_Time, Att_Code from attendanceDetswhere College_Year = 2005 AND group_code LIKE '____________' AND Student_ID LIKE '________'AND Session_Date <= '2005-11-16' AND Session_Date >= '2005-11-16' AND Att_Code LIKE '_' Print 'First query took: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VarCHar(5)) + ' milli-Seconds' Set @Time = GetDate() EXEC CAMsp_Att @College_Year = 2005, @Start_Date = '2005-11-16', @End_Date = '2005-11-16' Print 'Second query took: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VarCHar(5)) + ' milli-Seconds'GO --SET SHOWPLAN_TEXT OFF--GOThe execution plan for the first query appears miles more costly than the sproc yet it is effectively the same query with no parameters. However, my understanding is the cached plan substitutes literals for parameters anyway. In any case - the first query cost is listed as 99.52% of the batch, the sproc 0.48% (comparing the IO, cpu costs etc support this). BUT the text output is:(10639 row(s) affected) First query took: 596 milli-Seconds (10639 row(s) affected) Second query took: 2856 milli-SecondsI appreciate that logical and physical performance are not one and the same but can why is there such a huge discrepancy between the two? They are tested on a dedicated test server, and repeated running and switching the order of the queries elicits the same results. Sample data can be provided if requested but I assumed it would not shed much light. BTW - I know that additional indexes can bring the plans and execution time closer together - my question is more about the concept. If you've made it this far - many thanks.If you can enlighten me - infinite thanks.

View 10 Replies View Related

Execution Procedure Stored During Execution Of The Report .

Aug 3, 2007



Hello :

How to execute a procedure stored during execution of the report, that is before the poster the data.

Thnak you.

View 4 Replies View Related

Execution In Scheduled Job Vs Direct Execution

Nov 29, 2004

Here's my case, I have written a stored procedure which will perform the following:
1. Grab data from a table using cursor,
2. Process data,
3. Write the result into another table

If I execute the stored procedure directly (thru VS.NET, or Query Analyser), it will run, but when I tried to execute it via a scheduled job, it fails.

I used the same record, same parameters, and the same statements to call the stored procedure.

Any idea?

View 3 Replies View Related

Actual Execution Plan Vs Estimated Execution Plan

Jul 7, 2006

The benefit of the actual execution plan is that you can see the actual number of rows passing through each step - compared to the estimated number of rows.But what about the "cost percentages" ?I believe I've read somewhere that these percentages is still just an estimate and is not based on the real execution.Does anyone know this and preferable have a link to something that documents it?Thanks

View 1 Replies View Related

Generating Numbers In SQL

Jan 19, 2004

hi,

I am developing a ASP.NET application using SQL Server as my database.
I want to create a number that is unique.

The number will have 8 digits.

first 2 digits correspond to year. ex:04 or 03

the next 6 digits start with 000001 and it should get added for each new entry of data.

i am not able to generate number in the way i said. I am relatively new to SQL. so any suggestions as how to go about solving the problem???. Are there any samples/codes available for this.


Any help would be highly appreciated.

thanks,
-sriram

View 7 Replies View Related

Generating SQL Script

May 31, 2001

Hi all,

I have generated an SQL script such that when it is run, it will automatically create tables if not existing or drop the existing tables. The problem I have is that, is there any way I can also make the script create not only the tables but also transfer the data from the old database to the new database?

I would really appreciate if any of you could point me to any existing programs, scripts etc that can do that. Thanks in advance

Regards,
Celia

View 1 Replies View Related

Generating SQL Scripts

Oct 8, 1999

Is there a way to generate SQL Scripts from a stored procedure?

For disaster recovery and documenation reasons, we'd like to create scripts of our databases periodically. I, being somewhat lazy, would like to automate this so I really don't have to remember to run this every so often.

Any help would be appreciated.

View 1 Replies View Related

Generating SQL Scripts

Jun 7, 1999

Is there a way to generate SQL scripts from other than SQL Enterprise Manager?

View 3 Replies View Related

Generating Nested XML

Oct 29, 2014

I must produce an XML file with this layout:

Code:
<root>
<dataroot>
<Professions>
<Profession>web designer</Profession>
<tags>
<tag>Ruby on Rails</tag>
<tag>Apache</tag>
<tag>HTML/CSS</tag>

[code]...

I can't get my head around it ..I've even tried to solve it by using FOR XML RAW.

View 3 Replies View Related

Generating A Leading Zero

May 13, 2008

Hi

I am unable to see how to generate a leading zero.



Table A

declare @TableA table ( ID numeric ,
Fruits varchar(10)
)
insert @TableA
select 1,'Oranges'
union all select 2,'Mangoes'
union all select 3,'Apricots'


ID Table A
1 Apples
2 Oranges
3 Grapes
4 Apricots


declare @TableB table ( seed numeric ,
)
insert @TableB
select 080513000448
union all select 080513000449
union all select 080513000450

Table B
seed
080513000448
080513000449
080513000450


I wrote the following query but i need generate a leading zero not sure which function can help maybe the right function but i am not sure how to use it in this case


SELECTconvert(varchar(10), getdate(), 12) +
(SELECT
CASE
WHEN SUBSTRING(ISNULL(max(seed),'00000'),1,6) = convert(varchar(10), getdate(), 12)
THEN SUBSTRING(ISNULL(max(seed),'00000'),7,12)
ELSE '000000'
END AS SEED
FROM B)
+ (row_number()
over (order by Id))
as SEED
FROM A



SEED
----
80513000451



The output which i need is

SEED
----
080513000451

rather then

SEED
----
80513000451

regards
Hrishy

View 20 Replies View Related

Generating Numbers

May 12, 2007

A simple question! I would like to create a database that creates say serial numbers and saves them to a table. If poss I would like to create them on demend and not duplicate.....

I have blank page syndrome!!!!

Thanks in advance

View 2 Replies View Related

Generating A Cube ..

Feb 19, 2008

Can anyone suggest a case-study/reference links/video demos for creating a cube using Analysis Services and generation of reports using Reporting Services.

Thank You

View 2 Replies View Related

Generating Sequence

Mar 15, 2008

Guys,

I have to generate sequence for distinct group of values for example

intially seq is set to 1 through out the table

categorydescidseq
__________________________________
AccountingAccounting61
AccountingAccounting72
AccountingFinal81
AccountingFinal92
AddendumAddendum 101

Is there any way to accomplish this?

Any suggestions and inputs would help

Thanks

View 4 Replies View Related

Generating The Csv Files

Mar 31, 2008

hi

how can we generate the data in csv files of database tables by using sql query?


regards

View 3 Replies View Related

Primary Key Generating

Aug 24, 2006

Help again please,I need to insert rows into a table from another table. The tables areidentical column wise except the table im inserting from does not havea primary key value. On insert I need to generate a primary key thatis consecutive based on the table im inserting into. Also the table iminserting into does not have a identity column. Much appreciated.

View 7 Replies View Related

Generating Packages Using C#

May 12, 2006

Hi,

we would like to generate SSIS packages(connections, data flow tasks, etc..) programatically using C#. Is it possible to do so?

At present we are creating packages by dragging and dropping required componets from toolbox and configuring them manually. We have a requirement of creating around 300 packages every week. Manual creation of packages is really a nightmare.

I heard from one of friends that using c# we can create packages. is it true? if so, can we view/edit the packages generated using c# using VS 2005 IDE(if required) ?



Regards,

Gopi



View 4 Replies View Related

Generating Reports On Fly

Mar 29, 2007

Hello,



SRS 2005 provides functionality for loading and rendering reports on fly using LoadReportDefinition and Render methods defined in ReportExecutionService webservice.



I was wondering if the same/similar behaviour can be accomplished in Reporting Services 2000.



Thanks in advance,



Kobi

View 1 Replies View Related

Generating Script

Jul 12, 2007

What does N indicate in the following script

SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].xxxx' )

Plz can anyone explain

EXEC dbo.sp_executesql @statement = N'Create View xxxxx'

View 4 Replies View Related

Generating Sql Logins

Oct 17, 2006

I've been given an excel file that lists:

userID's,
Names
Password
expiary dates.

I need to convert this list into sql server logins. Can this be done via a tsql statement? if so how, and if it cant be done via a statement then what other way can it be done.

Many thanks in advance.

View 2 Replies View Related

Generating A Primary Key

Sep 18, 2007

I'm having problems generating the primary key for a sql server table. I use a slowly changing dimension to discriminate modified and new records. The primary key in the SQL Server table is a combo number/letter incremental (ex. 0000A, 0001A...9999A, 0000B...). I tried creating Instead of insert and For insert trigger for a table but this doesn't seem to do the work.



What are my other options? How can I generate a primary key for every new row?

Any advice is appreciated.
Regards
Sara

View 6 Replies View Related

Generating CREATE Scripts

May 20, 2008

Hello all,
I have a few questions relating to scripts and I'm looking for some help, or at least a point in the right direction.
1) I'm generating CREATE scripts to move a number of tables/procedures/functions from my testing database to my production database.  The issue I have is the script doesn't seem to be generated in a particular order, so I'm getting errors when a table/procedure/function is created that references something that hasn't been created yet.  Is there a setting available to create the script in a specific order to prevent these errors?
2) This is somewhat related to the first question.  I need to drop all the tables/procedures/functions before I re-create them.  Can I set all the DROPs at the beginning of the script, and then run all the CREATEs?
3) Eventually the database will be ready for production and in use.  And eventually I'll be asked to make changes, without erasing existing data.  Can I accomplish this using the generate scripts feature?  What topics should I be investigating when looking into conducting these types of updates?  "Updating database" is too general and I'm not sure the technical term.  I've done very simple DB-related tasks so I'm trying to figure out what I'm looking for so I can educate myself.  Basically, I don't know what I don't know.
Any help is appreciated.  Thanks.

View 1 Replies View Related

Generating Tables Programatically

Jun 7, 2008

hi i am trying to gernerate some tables ( first with a script and then with a stored procedure),, in ms server management studio exrress 2005 using the followingcreate table ip(    id int IDENTITY NOT NULL,    ip varchar(15) NOT NULL,    hostname varchar(128) NOT NULL,    primary key(id)) ; i store this in a new query (assume :right click associates the query to the current database ?) and run it and nothing happens  its been a while since i did sql but i think the sql is ok....how or more specifically where is the appropriate place to run this code  thankssimo  stored as a new query     

View 1 Replies View Related

Generating Text File

Dec 4, 2003

I have a table(say tblUserInfo) on SQL Server. What I like to do is a text file will be generated on the hard drive which SQL Server sits on when a new record is inserted into tblUserInfo. The content of the text file comes from the table. Is there any way we can go for doing that?

View 2 Replies View Related

Generating Membership Number

Jan 20, 2005

Hi all,
I have a question about generating membership numbers on the fly when someone registers to my website.

Rather than using the auto increment field as a membership number, I would rather keep it as just as the ID for the record and I would like to have a seperate membership number that looks something similar to this...

SR357324J

This will then stay with them for the lifetime of their membership and be on their printed loyalty card.

My questions are...
1) Is there a 'good practice' for membership number format and generation?

2) If this was used as a unique field, is there a degradation in performance when looking up records due to it being alphanumeric.

I may be well off base here, however these are my thoughts so far and your opinion/help is greatly appreciated.

Thanks for your contribution.

View 2 Replies View Related

Generating A Database Design From Xml

Apr 21, 2006

Hi, I'm looking for a way to take an XML feed and basically do what this article describes: http://msdn.microsoft.com/msdnmag/issues/03/05/MetaDataServices/

(In short, take in XML, generate a database generation script, creating that database, then load the XML data into said database.)

But, the above article involves user interaction and I would like this all automated, ie: in .NET 2.0/C#

Does anyone know how to do this?

Cheers,

Matt.

View 1 Replies View Related

Auto Generating An Email

Jun 5, 2006

Hi All,
I need to opening up a browser page everyday at 6am. (What I am trying to do is to send an email with some formatted output everyday at 6am)
Is there anyway to achive this via SQL 05 Express?
Thanks,
Mal.

View 4 Replies View Related

GENERATING A LIST OF DATES

Jan 25, 2002

Can someone show me how to load a list of dates into a table.
(i.e. from Jan. 1, 1995 to Jan, 1, 2005)

Thanks in advance!
BV

View 1 Replies View Related

Generating Serial Codes

Jul 29, 2001

Hi,

I have a table with a primary key, what I really need is something like an IDENTITY, but with the character 'X' and the last to digits of the year added on the front. Is there another way to update the field automatically like an IDENTITY would do, automatically incrementing as fields are inserted.

View 1 Replies View Related

Generating Surrogate Key Without IDENTITY

Jan 22, 2001

Hello
I'm looking for a way of generating the next key value that works in MS and Sybase SQL Servers. Sybase identity columns are a bit dodgy, so...

If I have a separate table NextKey (NextKey int) with one row that I update as follows...

declare @NextKey int
update NextKey set NextKey = NextKey + 1, @NextKey = NextKey + 1
insert into myTable (PrimaryKeyCol, ....) values (@NextKey, ....)

are there any problems with concurrency ? As I see it the update will lock the row so different connections will always come up with a different @NextKey value....

Thanks
John

View 2 Replies View Related

Generating SQL Script Of Database

Jan 3, 2000

Hi,
is there a way in SQL Server 7, to use some sql to extract the entire database schema?
I know that with the Enterprise manager there is All Tasks- Generate SQL Scripts.
But I need to connect to the database and use some sql to do this.
Any suggestions?

Thankyou
RH

View 1 Replies View Related

Generating Scripts For An Application

May 8, 2002

Hi there..

We are developing an INtranet application using SQL Server 2000 and ASPs, we are looking for a way to Generate a Module wise Scripts(Database Scripts like Table Scripts / Stored Procedure / Triggers / Functions etc).

This facility exits in Oracle 8i. How can i do the same in SQL Server2000..??

When i say Application..I mean Our Application and When i say Module..I mean one of the Modules in the Application..

Does SQL Server 2000 have any utility to do the same..???

If I am able to do this..then the COde Maintainablility will be easier...

Thanx

regards
Murali

View 1 Replies View Related







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