Alternative To Dynamic Sql?

Jul 20, 2005

I have a procedure that take several paramters and depending of what
values is submitted or not, the procedures shall return different
number of rows. But to simplyfy this my example use just one
parameter, for example Idnr.

If this id is submitted then I will return only the posts with this
idnr, but if this is not submitted, I will return all posts in table.
As I can see I have two options
1. IF @lcIdNr IS NOT NULL
SELECT *
FROM table
WHERE idnr = @lcIdNr
ELSE
SELECT *
FROM table

2. Use dynamic SQL.

The first example can work with just one parameter but with a couple
of different input paramters this could be difficult, anyway this is
not a good solution. The second example works fine but as I understand
dynamic sql is not good from the optimizing point of view. So, I don't
want to use either of theese options, so I wonder If there i a way to
work around this with for example a case clause?

Regards
Jenny

View 3 Replies


ADVERTISEMENT

Alternative To Dynamic SQL In A Function

Jan 14, 2008

Hi all, I have a sql problem i'd like to put to the masses because its driving me crazy! Before I start, this is a database i inherited so I cant change the schema.
I have a table which holds field information for a form, namely the table name, column name and some other irrelevant stuff (X/Y coordinates for printing onto a document). Here is some sample data to explain better:
TableName              FieldName                Xpos             Ypos
----------               ---------                -----            -----
FruitTable                FruitName                10                20
VegTable                 VegName                 10                40
FruitTable                FruitColour               20                10  
(Thats not the real data of course)
What I need is a calculated field which returns the value of each field from each table – probably by constructing a dynamic sql statement(?) It would look something like this:
Select @FieldName From @TableName Where bla bla bla â€“ don’t worry about the where clause. The completed dataset will hopefully then look like this:
TableName              FieldName                Xpos             Ypos             FieldValue (calculated field)
----------               ---------                -----            -----            ---------      
FruitTable                FruitName                10                20                Oranges (result of: Select FruitName From FruitTable Where....)
VegTable                 VegName                 10                40                Parsnips (result of: Select VegName From VegTable Where....)
FruitTable                FruitColour               20                10                Green (result of: Select FruitColour From FruitTable Where....)
 
I have tried creating a scalar-valued function which takes TableName and FieldName as parameters and creates a dynamic sql string, but i cannot seem to execute the sql once I have built it. Here is a general idea of how I was trying to use the function:
Main query:Select
TableName, FieldName, Xpos, Ypos,
dbo.GetFieldValue(TableName, FieldName) As FieldValue
From
tblFieldAndPosition---------------Function: CREATE FUNCTION GetFieldValue (@TableName nvarchar(255),@FieldName nvarchar(255))

RETURNS nvarchar(255)
AS
BEGIN

Declare @SQL nvarchar(max)
Set @SQL = 'Select ' + @FieldName + ' From ' + @TableName

sp_executesql @SQL??

return ???

END ------------------------- The alternative to getting this data all out at once is contructing the sql statement in code and going back to the database once for every row - which i really dont want to do. If anyone has had a situation like this before, or can point me in the right direction I will be very very grateful.  Hope thats clear. Thanks in advance   

View 5 Replies View Related

Dynamic WHERE Clause Alternative.

Jun 26, 2007

I have 3 tables: Authors, Employee and Stores.
I need to create a stored procedure which will take 3 comma delimited parameters, to be able to query above 3 tables.
Basically my front end user can say give me Authors with last name starting from ‘A,B’ and Employee with first name starting from ‘J,N,K’ and Stores with city starting from ‘New, Los’.
So, stored procedure call will look like this Exec myStoredProcedure 'A,B' , 'J,N,K', 'New,Los' .

My question is, how should I handle WHERE clause in stored procedure if I don’t want it to be a “dynamic WHERE�.

Thank you

View 1 Replies View Related

Alternative To Dynamic SQL In A Function

Jan 15, 2008



Hi all, I have a sql problem i'd like to put to the masses because its driving me crazy! Before I start, this is a database i inherited so I cant change the schema.


I have a table which holds field information for a form, namely the table name, column name and some other irrelevant stuff (X/Y coordinates for printing onto a document). Here is some sample data to explain better:

TableName FieldName Xpos Ypos
---------- --------- ----- -----
FruitTable FruitName 10 20
VegTable VegName 10 40
FruitTable FruitColour 20 10

(Thats not the real data of course)

What I need is a calculated field which returns the value of each field from each table €“ probably by constructing a dynamic sql statement(?) It would look something like this:

Select @FieldName From @TableName Where bla bla bla €“ don€™t worry about the where clause. The completed dataset will hopefully then look like this:

TableName FieldName Xpos Ypos FieldValue (calculated field)
---------- --------- ----- ----- ---------
FruitTable FruitName 10 20 Oranges (result of: Select FruitName From FruitTable Where....)
VegTable VegName 10 40 Parsnips (result of: Select VegName From VegTable Where....)
FruitTable FruitColour 20 10 Green (result of: Select FruitColour From FruitTable Where....)

I have tried creating a scalar-valued function which takes TableName and FieldName as parameters and creates a dynamic sql string, but i cannot seem to execute the sql once I have built it. Here is a general idea of how I was trying to use the function:
Main query:Select
TableName, FieldName, Xpos, Ypos,
dbo.GetFieldValue(TableName, FieldName) As FieldValue
From
tblFieldAndPosition---------------Function: CREATE FUNCTION GetFieldValue (@TableName nvarchar(255),@FieldName nvarchar(255))

RETURNS nvarchar(255)
AS
BEGIN

Declare @SQL nvarchar(max)
Set @SQL = 'Select ' + @FieldName + ' From ' + @TableName

sp_executesql @SQL??

return ???

END ------------------------- The alternative to getting this data all out at once is contructing the sql statement in code and going back to the database once for every row - which i really dont want to do. If anyone has had a situation like this before, or can point me in the right direction I will be very very grateful. Hope thats clear. Thanks in advance

View 7 Replies View Related

Column Name As Parameter Dynamic Sql Alternative

Feb 11, 2008

I am unfortunately lumered with a table I cannot edit that stores a division 2 characters along with boolean fields '0506', '0607', '0708' ... (academic years) to represent if that particular division is active in the current academic year. This has made me have to resort to dynamic sql within a stored procedure to input the appropriate field names.

Is there a better way to do it, it's not mission critical to make it not use dynamic sql but I would prefer not to.

View 2 Replies View Related

Alternative For DTC

Jul 30, 2007

Hi,
i am using the DTC in my code to connect to two different servers on the network through a SQL query which is unfortunately very slow; can u please guide me with an alternative for the same

Thanks

View 17 Replies View Related

Alternative Way

Jul 20, 2005

SELECT *FROM organizationWHERE (departmentID = divisionID) AND (divisionID = branchID) AND(branchID = sectionID) AND (sectionID = unitID)Is there anyway I can make this query more simlified w/o repeating thesame column in the where clause?thankss/RC

View 3 Replies View Related

@@IDENTITY Alternative

Dec 30, 2007

Hi everyone,
I'm trying to come up with a replacement for @@IDENTITY, because I have SQL code I want to make more portable.

Original:ID = MyDataLayer.Execute("INSERT INTO X(a,b,c) VALUES(A,B,C); SELECT @@IDENTITY")
Proposed solution:
lock(MyDataLayer)
ID = MyDataLayer.Execute("SELECT MAX(id)+1 FROM X")
if(ID==null) ID=1
MyDataLayer.Execute("INSERT INTO X(id,a,b,c) VALUES(ID,A,B,C)")
unlock(MyDataLayer)
(This is of course pseudocode, for SQL Server I'd need SET IDENTITY_INSERT.)

Do you think the preceding solution is equivalent to the original?
Do you know something better?

Equivalent should mean here, not necessarily generating the same ID's,
but maintaining functionality and consistence all over the database.

View 9 Replies View Related

Alternative To Using USE In A PROCEDURE

Apr 7, 2004

is there a way to get around not using USE in a PROCEDURE?

I need to because I have a main site that inserts information into other DB's that i use for various subdomains. But without being able to use USE i cant select which database is needed.

thx in advance

View 2 Replies View Related

Immediate IF ALTERNATIVE REQUIRED

May 14, 2001

Hi!

I wrote a query in MS-ACCESS using IIF. Is there any way to convert it to SQL Server Query to do the same job as it do in MS-ACCEESS

e.g. Here is manipulation with one column that I did in MS-ACCESS

IIf(InStr(1,Destinations.[Destination Name],"-",1)-1<0,Destinations.[Destination Name],Left(Destinations.[Destination Name],InStr(1,Destinations.[Destination Name],"-",1)-1)) AS COUNTRY,

View 1 Replies View Related

MSDE Alternative

Jan 26, 2004

My company develops software that is distributed to thousands of customers. We chose MSDE as the database engine. Over the past 4 months, we have spent countless hours with customers, Microsoft, Installshield and web searches trying to resolve issues with installing MSDE. The issues seem to vary by customer and most take a great deal of support time. We understood MSDE to be a product that requires little support but in hindsight, it appears that it requires a great deal of knowledge just to get installed. We make small steps but no leaps forward.

It has come time to evaluate other products. If there is a magic bullet, I would love to hear about it. In its absence, does anyone have success to share with other products?

Phil

View 13 Replies View Related

Alternative To SQLXMLBULKLOAD ?

May 2, 2008

Hi,

Just curious, is there any alternative to SQLXMLBULKLOAD for shredding and loading very large (800 megs) XML files ? Due to the nature of the XML data sent to me (which I have no control over)I am having great difficulty loading data into tables. More specifically, I can load parent data but not the child data beneath it despite using sql:relationships.

Thanks,
Jeff

View 2 Replies View Related

Cursor Alternative ?

Mar 30, 2006

Hi,

In a stored procedure I'm processing, via a cursor, a table of, potentially, 100,000 rows on a daily basis. The only column in each row is a 12-byte transaction control number. I know that using cursors can cause performance issues. Is there an alternative to using a cursor that has less of a performance impact ?

Thanks,
Jeff

View 5 Replies View Related

Alternative To Cursor..

Jul 18, 2006

Hi All,

Beside cursor, what else can i use to speed up my processing? Now i have about 2mils rows need to update using one daily reference table(30k rows).

Thanks,
Jack

View 10 Replies View Related

Index Alternative

Feb 14, 2007

Cameron writes "Thanks for taking a look @ my question....

Basically, is there an alternative to indexing that maintains the fast searching capability (or possibly faster)?

We maintain over 500 databases on a single SQL server and currently (the way I am told) the server is limited to indexing 256 databases, so we have to basically create a new database with ALL the searchable data and use it for searches. While this works, it seems like there should be an alternate method. Any suggestions?

Thank you for your time!"

View 3 Replies View Related

Alternative To ODBC

Mar 1, 2007

are there any alternatives to linking my db to webserver other then ODBC such as direct dsn connection

View 4 Replies View Related

Alternative To Sqlvarient

Sep 24, 2007

i need some alternative to sqlvarient..

View 2 Replies View Related

Alternative To IF In This Case

Oct 24, 2007

Hey guys,
What would be a better / more elegant solution to something like

IF(@areaCode = '111' OR @areaCode = '222' 0R @areaCode = '333')
BEGIN
//do something
END

that uses less OR's in an IF statement?

View 6 Replies View Related

Alternative To Triggers

Feb 1, 2008

Hi all

I work as a production dba and our development team are trying to push a project which involves using triggers. The aim is to transfer information between to databases (on two differents servers) because currently users have to type in the same info into the two different systems.
The triggers will be defined on a couple of tables, checking for inserts, updates, deletes, and then insert this into staging tables within teh same database. However the trigger does more complex processing than just inserting the same records from the production table into the staging table. Because the schema between the source database and destination database is different, the trigger needs to do some manipulation before it updates the staging tables. It basically does massive selects from a number of different tables to get the desired column list & then puts that into the staging tables.
We have basically asked them to reimplement this solution using other methods (such as timestamping the necessary tables and then putting the trigger login into a stored proc and scheduling it to run through a job).

However, we've found out the triggers make use of the 'deleted' and 'inserted' special trigger tables to compare new data to old data - i.e. not all inserts/updates/deletes need to be pushed to the staging tables - it depends on certain criteria based on this comparison of old and new data.....that throws a spanner in the works. What alternatives could provide this functionality, without just making the whole process a a headache to maintain - which is why we recommended not using triggers in the first place!!

Sorry for the long post - needed to explain the issue properly. Hopefully some of you will be able to provide some feedback - teh sooner the better as I have a meeting with the developers later today and would like to offer some alternatives.

Thanks!
Div

View 2 Replies View Related

Alternative To OpenXML

Mar 17, 2006

Hi All,I want to pass XML and the data in the XML should be stored in thetables of the database. However, I do not want to use the OpenXMLstatement. Please let me know.Regards, Shilpa

View 2 Replies View Related

Alternative To Identity Help.

Aug 20, 2007




I have a SSIS package that imports an Excel file using Visual Basic 2005 into my SQL 2005 database. All the fields are the same in the DB and xls. The package runs with no problem but I need one of the fields to be autoincrement. I tried to set up the properties of one of my fields "ID" to be an Identity. This didn't seem to work at all. There are about 1300 records in the DB so far with the last "ID" number being 10001415. Before now, the numbers were inputed manually. I want the "ID" to be assigned when the SSIS package imports the xls file.


Any help is very appreciated.

View 8 Replies View Related

SCD Component - Is There An Alternative?

Nov 21, 2006

Hi,

I have several SCD components in my project. As I have to process millions of records, SCD's are taking a lot of time. Is there a way to speed them up? Work arounds?

Any tip is welcome

-Tom

View 21 Replies View Related

What Is The Alternative To Go Under Sqlcmd?

Oct 8, 2006

Hi

I found it a bit annoying to type Go after some very simple query and I wonder is there a short cut to execute the query i type right after I press enter?

1> select * from Table
2> go <enter>

instead, how to you execute line 1 without entering go?

Thank you

View 7 Replies View Related

Anoter Alternative

Jul 27, 2007

There is another alternative ... if you are using MSCRM 3.0

follow this steps

1. Navigate to your report manager --> //<<Server Name>>/reports
2. Navigate to your datasource --> HOME > <<Datasource>>
3. At the top tab select Properties
4. Make sure that there is a user role call "NT AUTHORITYNETWORK SERVICE" thus apply the appropiate roles for it


Vula....!!!!!

View 1 Replies View Related

IF ELSE Alternative For Stored Procedure

May 1, 2007

Hi,I'm trying to create a stored procedure that checks to see whether the parameters are NULL. If they are NOT NULL, then the parameter should be used in the WHERE clause of the SELECT statement otherwise all records should be returned.sample code: SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetProjectInfo]
(@ProjectTitle varchar(300), @ProjectManagerID int, @DeptCode varchar(20), @ProjID varchar(50),
@DateRequested datetime, @DueDate datetime, @ProjectStatusID int)
AS
BEGIN
SET NOCOUNT ON
IF @ProjectTitle IS NOT NULL AND @ProjectManagerID IS NULL AND @DeptCode IS NULL AND @ProjID IS NULL AND @DateRequested IS NULL AND @DueDate IS NULL AND @ProjectStatusID IS NULL
SELECT ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusID
FROM dbo.tbl_Project
WHERE ProjectTitle = @ProjectTitle;
ELSE IF @ProjectTitle IS NOT NULL AND @ProjectManagerID IS NOT NULL AND @DeptCode IS NULL AND @ProjID IS NULL AND @DateRequested IS NULL AND @DueDate IS NULL AND @ProjectStatusID IS NULL
SELECT ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusID
FROM dbo.tbl_Project
WHERE ProjectTitle = @ProjectTitle AND ProjectManagerID = @ProjectManagerID;
ELSE
SELECT ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusID
FROM dbo.tbl_Project; I could do this using IF-ELSE but that would require a ridiculous amount of conditional statements (basically 1 for each combination of NULLs and NOT NULLs). Is there a way to do this without all the IF-ELSEs? Thanks. 

View 34 Replies View Related

Speed Up The Process..how? Alternative Way?

May 5, 2008

Good Day, please help me, i have a data driven site that displays computed data to a Complete Gridview, this is my problem, when i run my site, it all displays the informations and the gridview a i intended to. generally my site works fine BUT!!!!! my site have different levels of display, like thisSite 1:it has 3 dropdown menus, dropdown 2 is dependent to dropdown 1 and dropdown 3 is dependent to dropdown 2 and the complete gridview is dependent to dropdown 3, it is a postback process of dependencies, i hope you get what i mean, Site 2:this is the same as the site 1 but this time, is has 2 dropdown menus only, and the complete gridview has a gridview in side of it thru details template. Site 3: almost the same as Site 1 and 2 but this time there will be no dropdown menus, only the complete gridview, but the gridview has 3 levels, the same level sa the level of dropdown menus in site 1, where the main gridview has a gidview below it and a gridview again below it and soon,  The Problem:the site works fine but it runs super SLOW!!! specially in the Site 3, where it has to display and compute different gridviews from the complete gridview at the same time, sometimes it cause the TIME OUT EXPIRE.that can i do to speed the process other than upgrading my server? is there an alternative?please helpfor more info this is the SQL codes that i used in each level in the complete gridviewThe highest level in the gridview of the initial display of the complete gridview: select rbu,count(distinct dslamname) as NumberOfDslam,
(select count(secode) from dslamdata as a where a.rbu = r.rbu ) as Capacity,
(select count(secode) from dslamdata as a where a.rbu = r.rbu and dnum > '1') as Used,
(select count(secode) from dslamdata as a where a.rbu = r.rbu and dnum < '1') as Remaining,
(select (select count(secode) from dslamdata as a where a.rbu = r.rbu and dnum > '1')*100/(select count(secode) from dslamdata as a where a.rbu = r.rbu )) as Utilization,
(select sum(dwlink) from dslamdata as a where a.rbu = r.rbu ) as Sold_Bandwidth
from dslamdata as r
group by rbu  The Second to the highest when you would click on the +/- button in the complete gridview  select aco,count(distinct dslamname) as NumberOfDslam,
(select count(secode) from dslamdata as a where a.aco = r.aco ) as Capacity,
(select count(secode) from dslamdata as a where a.aco = r.aco and dnum > '1') as Used,
(select count(secode) from dslamdata as a where a.aco = r.aco and dnum < '1') as Remaining,
(select (select count(secode) from dslamdata as a where a.aco = r.aco and dnum > '1')*100/(select count(secode) from dslamdata as a where a.aco = r.aco )) as Utilization,
(select sum(dwlink) from dslamdata as a where a.aco = r.aco ) as Sold_Bandwidth
from dslamdata as r where rbu = ?
group by aco  The Last in the display when you would click on the +/- of the second gridview select dslamname,
(select count(secode) from dslamdata as a where a.dslamname = r.dslamname ) as Capacity,
(select count(secode) from dslamdata as a where a.dslamname = r.dslamname and dnum > '1') as Used,
(select count(secode) from dslamdata as a where a.dslamname = r.dslamname and dnum < '1') as Remaining,
(select (select count(secode) from dslamdata as a where a.dslamname = r.dslamname and dnum > '1')*100/(select count(secode) from dslamdata as a where a.dslamname = r.dslamname )) as Utilization,
(select sum(dwlink) from dslamdata as a where a.dslamname = r.dslamname ) as Sold_Bandwidth
from dslamdata as r where aco
group by dslamname  if you would look at it, all the codes are the same except for the selected field, i hope someone can help me with this, thanks and good day SALAMAT PO., 

View 2 Replies View Related

SQL Programmer Tool Alternative

Dec 17, 2004

Hi, does anybody use/know a nice and convinient yet rich of functionality tool that could replace the SQL Programmer one?

What do you recommend to look at?

Forgot to mention, it must work with SQL, Oracle and Sybase databases/servers.

Thx
Dim

View 3 Replies View Related

Alternative To Link Server

Oct 10, 2007

Hi all,

I am having two databases(MS-Sql) on two different servers.Let say they are Server1 and Server2.I am having some stored procedures(sps) which are executing on Server1.Results given by these sps are 6 different tables(Theses tables are temporary tables e.g #Table1 and they are created in one of the sps on Server1). And I want to use these 6 tables on Server2.
But constraint here is, i can create link server for Server1 from Server2 but not from Server1 to Server2.So i can not access Server2 directly from Server1.
Even if i am using custom tables here instead of temp tables(#) it will take me to solution but that is again a constraint i can not do this.
Is there any alternative solution for Link server in this case?
I dont want to go for OPENROWSET and OPENDATASOURCE b'coz of performance issue.

Thank you in advance!!

Kedar Waghmode.

View 9 Replies View Related

Alternative For The LIMIT Clause

Aug 9, 2004

Hello,

What do you do if you need to select item 20 to 40 from a table? Do you just do 1 to 40 and let PHP ignore the first 20, or do you have another equivalent of the MySQL LIMIT Clause for MSSQL?

View 14 Replies View Related

Alternative To Joining Views

Dec 4, 2006

Hi,

I have 2 views that I can join to give me the recordset I want. Both views are currently filtered on a particular column (ie 'WHERE colName = myValue')

The problem is that I want to use this from a web page with user input in which the user would specify myValue.

Is there any alternative to having two views? Can I combine them into one SQL statement? Access allowed me to do it by specifying an alias for a select statement, then joining this to another. I'm not sure if that makes sense, but I guess what I want to do it specify one view then another, then join them - all in the same statement!

Something like this:


Code:

SELECT view1.*, view2.*
FROM ((SELECT tbl1.*,tbl2.*,tbl3.*
FROM tbl1 JOIN tbl2 ON tbl1.id = tbl2.tbl1_id
JOIN tbl3 ON tbl2.id = tbl3.tbl2_id
AND tbl3.id = myValue) AS view1)
((SELECT tbl4.* FROM tbl4 WHERE tbl4.id = myValue) AS view2)
RIGHT JOIN view1 ON view2....

View 1 Replies View Related

Bulk Insert Alternative?

Jan 8, 2008

I have data in an xml format...


<Store1>
<sale>
<productid>123</productid>
<salesperson>bob</salesperson>
<time>12:00</time>
</sale>
<clockedin>
<salesperson>bob</bob>
<time>11:45</time>
</login>

etc....

</Store1>


My database tables are as follows:

table1
id (identity field - primary key)
storeid (varchar)
event (varchar)
time (datetime)

table 2
id *
name (varchar) *
value (varchar)
* primary key

Using the above xml data example, my records would look like this in the tables:

Table 1
id: 1
StoreID: Store1
Event: sale
Time: 12:00

id: 2
StoreID: Store1
Event: clockedin
Time: 11:45

Table 2
id: 1
Name: productid
Value: 123

id: 1
Name: salesperson
Value: bob

id: 2
Name: salesperson
Value: bob



Soooo, now I can handle this programatically by reading my xml data and inserting this data into the database as its read by the xml parser. But, what I'm wondering is if there is faster way of doing this. Possibly using some sort of bulk insert?

The xml data files will at times be massive. For example during the holiday season, there could be millions of records needing to be inserted. However, there will also be days when the data is small.

View 6 Replies View Related

Alternative Way To PIVOT Function?

Jan 14, 2014

We are running SQL2000, and I was trying to use the PIVOT function, as you are all aware 2000 does not support the PIVOT function.

is there an alternative to the PIVOT function

here my code I was trying to use

Code:
SELECT CostSavingsNo, CostSavingType

From (Select CostSavingsNo, CostSavingType, SavingsValue FROM CostSavingsDetails) CSD

PIVOT

(SUM(SavingsValue) FOR CostSavingType IN ([1], [2], [3], [4], [5], [6], [7], [8],[9], [10], [11], [12]], [13], [14])) AS PVT

View 7 Replies View Related







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