Building Dynamic Tsql Statements In A Loop
Jul 20, 2005
Hi;
I would like to read a list of tables from a temp table and then do a
sql statement on each table name retrieved in a loop, ie:
-- snip cursor loop where cursor contains a list of tables
declare @rec_count int
set @rec_count = 0
exec('select @rec_count = count(myfield) from ' + @retrievedTableName
)
This does not work. SQLSERVER tells me @rec_count is not declared.
How can I get the @rec_count populated....or can I?
Thanks in advance
Steve
View 3 Replies
ADVERTISEMENT
Jan 6, 2003
Hi,
I am having some problems trying to build an sql statement from more than one statement.
Here is the statement
select 'Insert App_Column (Table_ID, Column_Type_Transformation, Column_Name, )
Values (@table_ID,' ,'NULL,', name from payatwork..syscolumns where id in (
select id from payatwork..sysobjects where name like 'Employee_Profile')
order by colorder, ')'
What I am finding is that the bracket at the end of the statement is not appearing - how do I append statements to the end of this sql statement (i've tried various combinations of the + sign and the comma without success.
thanks,
Jim
View 4 Replies
View Related
Oct 24, 2007
I am doing backup job using windows 2003 backup. I hv several scheduled backup job. And sometimes, when the first backup job not yet finish backing up, the second backup job will automatic starts at the set time resulting 2 backup jobs at 1 time. I need to know whether in SQL2000/2005, we can set the tsql command to do the job queue for the windows 2003 backup job, meaning if the 1st backup job is still ongoing, the 2nd backup job will hv to wait even though the set time for the job was already expired. When the 1st job done, then only the 2nd job started.
Appreciate the feedback..
tq
View 1 Replies
View Related
Jan 17, 2008
Let me start by asking that no one try to convince me to use Stored Procs. The examples below are a lot more simplistic then my real world code and it just gets too complicated to try to manage the quantity of SPs that I would need.
I have an application that displays a lot of data and I've created a system for users to filter the data using checkboxlist controls, dropdown controls, etc. From this, I have a "core" query that selects the fields that display in my GridView. It has a base Select clause, From clause and Where clause. From this I then add more to the Where clause to apply these filter values.
Here's an example "core" query:
SELECT Profile.FirstName, Profile.LastName, Project.ProjectNameFROM Profile, ProjectWHERE Profile.ProjectCode = Project.ProjectCode
From this if a user want's to only display profiles from NC, they could select that from the CBL and the query would be modified to:
SELECT Profile.FirstName, Profile.LastName, Project.ProjectNameFROM Profile, ProjectWHERE Profile.ProjectCode = Project.ProjectCodeAND Profile.State IN ('NC')
My code would add the last line above since the user specified that they only wanted NC profiles.
This is very simple and I have this already going on with my application. Here's the problem. In order to accommodate all of the various filters, I have to inner join and left join a bunch of various tables. Many times I include tables that have no data to display or filter on and therefore impacts performance. Here's an example:
SELECT Profile.FirstName, Profile.LastName, Project.ProjectNameFROM Profile, Project, AgentWHERE Profile.ProjectCode = Project.ProjectCodeAND Profile.AgentID = Agent.AgentID
From the query above, I have included the Agent table that holds the agent's contact information. One of my filters allows the user to type in an agents name to find all profiles assigned to it. Here's what that would look like:
SELECT Profile.FirstName, Profile.LastName, Project.ProjectNameFROM Profile, Project, AgentWHERE Profile.ProjectCode = Project.ProjectCodeAND Profile.AgentID = Agent.AgentIDAND Agent.Name = 'Smith, John'
You can see now that it was necessary to have the Agent table already joined into the query so that when I used the agent name filter, it wouldn't crash out on me.
The obvious thing would be to only include the Agent table when searching for an agent name. This is ultimately what I'm looking to do, but I need a solid method to go about doing this. Keep in mind that I currently have 16 tables in my "core" query and many of those are not needed unless the filters call for it.
If anyone has any ideas on how to simplify this process I'm selcome to suggestions. We're using SQL 2000, but are looking to upgrade to SQL 2005, if that makes any difference. I know that the way I do table joins is compliant with SQL 2005 and I'm certainly open to suggestions that will make it forward compatible.
This app is using .NET 2.0 and written in VB.NET. Thanks for any help!
View 14 Replies
View Related
Mar 30, 2005
Hi
I am in the very final stages of
building a dating app for a client, I am totally stuck with the
advanced search page. been googling for days with limited success
For the most basic of purposes I have added a few form fields to my search.aspx page;
county (Dropdown list)
min age (Dropdown list)
max age (dropdown list)
Smoker (check box)
keyword (textbox)
My codebehind passes the vars to a stored procedure
@county = me.county.selectedvalue
etc
My problem is the stored procedure I sort of have the following but I can't get it to run
<code>
ALTER PROCEDURE dbo.TEST_ADVANCED_SEARCH
(@countyID int ,
@MaxAge
varchar(100),
@MinAge
varchar(100),
@smoker tinyint),
@keyword
varchar(250))
AS
DECLARE @SQL Varchar
(4000)
SELECT @SQL = 'dbo.user_accounts.profileComplete,
dbo.user_accounts.countyID, dbo.user_profiles.smoker,
dbo.user_profiles.Age
FROM dbo.user_accounts INNER
JOIN
dbo.user_profiles ON dbo.user_accounts.userID =
dbo.user_profiles.userID
WHERE (dbo.user_accounts.profileComplete =
1)'
IF @countyID > 0
SELECT @SQL = @SQL + ' AND
(dbo.user_accounts.countyID = @countyID)'
IF @MaxAge IS NOT
NULL
SELECT @SQL = @SQL + ' AND (dbo.user_profiles.Age <= @MaxAge)
'
IF @MinAge IS NOT
NULL
SELECT @SQL = @SQL + ' AND (dbo.user_profiles.Age >= @MinAge)
'
IF @smoker > 0
SELECT @SQL = @SQL + ' AND
(dbo.user_profiles.smoker = 1)'
IF @keyword IS NOT
NULL
SELECT @SQL = @SQL + ' AND (dbo.user_profiles.Description LIKE @MinAge)
'
EXEC(@SQL)
</code>
If I can get this to work I can add the remaining fields that I need
Am I Missing something glaringly obvious?
Any help or advice gratefully received
Thanks
View 3 Replies
View Related
Oct 24, 2000
We are migrating from a file-server Access Database to a SQL server backend and Access front end system. I'm using ADO to access the data off the server but am implementing most of the business logic in stored procedures. All logic was coded in VBA earlier but i'm having to move that to T-SQL for performance issues. In many cases I have dynamically constructed SQL statements in code but I'm having some trouble in T-SQL. How can I do this in T-SQL?
' This is some VB code that shows how the query differs based on a parameter.
If Me![Sorting] = 1 Then
Me![ControlNumber].RowSource = "SELECT [DVD_Projects_Table].[DVD_Number], [Title] & "" : "" & [ID_Number] AS Display,__
[DVD_Projects_Table].Date FROM [DVD_Projects_Table] __
WHERE ((([DVD_Projects_Table].System) = IIf([Forms]![DVD_Projects_Form]![SystemFilter] = 1, ""525"", ""625""))) And __
(([DVD_Projects_Table].Active) = IIf([Forms]![DVD_Projects_Form].[ActiveOnly] = True, Yes, __
[DVD_Projects_Table].[Active]))) ORDER BY [DVD_Projects_Table].Title;"
Else
Me![ControlNumber].RowSource = "SELECT [DVD_Projects_Table].[DVD_Number], [ID_Number] & "" : "" & [Title] AS Display,__
[DVD_Projects_Table].Date FROM [DVD_Projects_Table]__
WHERE ((([DVD_Projects_Table].System) = IIf([Forms]![DVD_Projects_Form]![SystemFilter] = 1, ""525"", ""625""))) And __
(([DVD_Projects_Table].Active) = IIf([Forms]![DVD_Projects_Form].[ActiveOnly] = True, Yes, __
[DVD_Projects_Table].[Active]))) ORDER BY Int(Right([ID_Number],Len([ID_Number])-4));"
End If
This is the ideal sp that would do what I want but it is obviously incorrect. How can I get this logic implemented. I need to construct an SQL query based on the input parameters in a stored procedure.
CREATE PROCEDURE [procDVDProjectsList]
@SortBy as bit,
@ActiveOnly as bit,
@SysFilter as integer
AS
SELECT
CASE @SortBy
/* Display Title first */
WHEN 0 THEN [DVD_Number], [Title] + " : " + [ID_Number] AS Display, [DVD_Projects_Table].[Date]
/* Display Number first */
WHEN 1 THEN [DVD_Number], [ID_Number] + " : " + [Title] AS Display, [DVD_Projects_Table].[Date]
END
FROM
[DVD_Projects_Table]
WHERE
CASE @SysFilter
/* List all */
WHEN 0 THEN
/* List only NTSC */
WHEN 1 THEN [DVD_Projects_Table].[System] = "525"
/* List only PAL */
WHEN 2 THEN [DVD_Projects_Table].[System] = "625"
END
AND
CASE @ActiveOnly
/* List All */
WHEN 0 THEN
/* List only active */
WHEN 1 THEN [DVD_Projects_Table].Active = True
END
ORDER BY
CASE @Sortby
/* Sort Alpha */
WHEN 0 THEN [DVD_Projects_Table].Title
/* Sort Numeric */
WHEN 1 THEN Right([ID_Number],Len([ID_Number])-4)
END
Thanks for your help,
-Sumit Malik
View 1 Replies
View Related
Apr 16, 2007
I have 2 tables, i am trying to write a query that will loop through table1, select 3 fields and populate table 2. All fields are nvarchar, using Sql2000...
View 2 Replies
View Related
Apr 19, 2008
Hi i have a page whereby the user can make a search based on three things, they are a textbox(userName), dropdownlist(subcategoryID), and region (regionID). The user does not have to select all three, he or she can enter a name into the textbox alone and make the search or enter a name into the textbox and select a dropdownlist value, my question is how can i build this procedure, this is what another user suggested but i am having trouble;
ALTER PROCEDURE [dbo].[stream_UserFind]
(
@userName varchar(100),
@subCategoryID INT,
@regionID INT
)AS
declare @StaticStr nvarchar(5000)set @StaticStr = 'SELECT DISTINCT SubCategories.subCategoryID, SubCategories.subCategoryName,Users.userName ,UserSubCategories.userIDFROM Users INNER JOIN UserSubCategories ON Users.userID= UserSubCategories.userIDINNER JOINSubCategories ON UserSubCategories.subCategoryID = SubCategories.subCategoryID WHERE UserName like @UserName'
if(@subCategoryID <> 0) set @StaticStr = @StaticStr + ' and SubCategories.subCategoryID = @subCategoryID 'if(@regionID <> 0) set @StaticStr = @StaticStr + ' and SubCategories.RegionId = @regionID '
exec sp_executesql @StaticStr
)
View 10 Replies
View Related
Apr 19, 2008
Hi i have a page whereby the user can make a search based on three things, they are a textbox(userName), dropdownlist(subcategoryID), and region (regionID). The user does not have to select all three, he or she can enter a name into the textbox alone and make the search or enter a name into the textbox and select a dropdownlist value, my question is how can i build this procedure, I tried this but it didnt work;
Code:
ALTER PROCEDURE [dbo].[stream_UserFind]
(
@userName varchar(100),
@subCategoryID INT,
@regionID INT
)
AS
declare @StaticStr nvarchar(5000)
set @StaticStr = 'SELECT DISTINCT SubCategories.subCategoryID, SubCategories.subCategoryName,
Users.userName ,UserSubCategories.userID
FROM Users INNER JOIN UserSubCategories ON Users.userID= UserSubCategories.userIDINNER JOIN
SubCategories ON UserSubCategories.subCategoryID = SubCategories.subCategoryID WHERE UserName like @UserName'
if(@subCategoryID <> 0)
set @StaticStr = @StaticStr + ' and SubCategories.subCategoryID = @subCategoryID '
if(@regionID <> 0)
set @StaticStr = @StaticStr + ' and SubCategories.RegionId = @regionID '
exec sp_executesql @StaticStr
)
View 2 Replies
View Related
Oct 14, 2004
Hi all,
I'm gonna need some help with this one.
I have this stored procedure written up that basically builds a dataset by querying a bunch of tables using outer joins. Our problem now is that it seems it takes a while for the dataset to pull back across the network. We would hence like to filter that dataset by adding on to the query in the procedure dynamically. Heres the query from the proc below:
SELECTN_Client.Prefix,
IsNull(dbo.N_CLIENT.SURNAME, '') + ', ' + IsNull(dbo.N_CLIENT.FIRST_NAME, '') AS Client_FullName,
dbo.N_CLIENT.TITLE,
dbo.N_COMPANY.COMPANY_NAME,
dbo.N_BUSINESS_UNIT.BUSINESS_UNIT_NAME,
dbo.N_DIVISION.DIVISION_NAME,
dbo.N_REF_INDUSTRY.INDUSTRY_NAME,
dbo.N_CLIENT.DIRECT_PHONE,
dbo.N_CLIENT.EMAIL,
dbo.N_CLIENT.TIER_ID,
(SELECT COUNT(Client_ID)
FROM N_Alumni
WHERE N_Alumni.Client_ID = N_Client.Client_ID) AS Alumni,
(SELECT COUNT(Client_ID)
FROM N_XREF_Client_Activity
WHERE N_XREF_Client_Activity.Client_ID = N_Client.Client_ID AND Activity_ID = 1) AS SandB,
(SELECT BAH_EMP_NID
FROM N_XREF_Client_Activity
WHERE N_XREF_Client_Activity.Client_ID = N_Client.Client_ID AND Activity_ID = 1) AS SandBMailer,
(SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'MM' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS MMEMPNID,
dbo.N_CLIENT.SURNAME AS Client_Surname,
dbo.N_CLIENT.FIRST_NAME,
dbo.N_CLIENT.FIRST_NAME AS Client_FirstName,
dbo.N_CLIENT.COMPANY_ID,
dbo.N_CLIENT.DIVISION_ID,
dbo.N_CLIENT.BUSINESS_UNIT_ID,
dbo.N_COMPANY.GROUP_ID,
dbo.N_CLIENT.COUNTRY,
dbo.N_GROUP.GROUP_NAME,
dbo.N_CLIENT.CLIENT_ID,
(SELECT IsNull(N_Vw_Client_BAH_Contact.First_Name, '') + ' ' + IsNull(N_Vw_Client_BAH_Contact.Surname, '')
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS PCFullName,
(SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS PCEMPNID,
(SELECT NMT_Practice_Code
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS NMT_Practice_Code,
(SELECT NMT_Practice_Name
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS NMT_Practice_Name,
#returnTable.AddlFullName,
#returnTable.AddlEMPNID,
#returnTable.FunctionID as Function_ID,
#returnTable.FunctionName as Function_Name,
(SELECT IsNull(N_Vw_Client_BAH_Contact.First_Name, '') + ' ' + IsNull(N_Vw_Client_BAH_Contact.Surname, '')
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'CSO' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS CSOFullName,
(SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'CSO' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS CSOEMPNID,
ISNULL(dbo.N_CLIENT.ARCHIVE_FLAG, 'N') AS Archive_Flag,
dbo.N_COMPANY.TARGET_COMPANY_FLAG,
dbo.N_COMPANY.INDUSTRY_ID,
N_Client.Address1,
N_Client.Address2,
N_Client.Address3,
N_Client.Address4,
N_Client.Address5,
N_Client.City,
N_Client.State,
N_Client.Postal_Code,
N_Client.Country,
N_Client.Region,
N_Client.Office_Code,
N_Client.Broderick_Target_Flag
FROMdbo.N_CLIENT
INNER JOIN
dbo.N_COMPANY ON dbo.N_CLIENT.COMPANY_ID = dbo.N_COMPANY.COMPANY_ID
LEFT OUTER JOIN
dbo.N_GROUP ON dbo.N_COMPANY.GROUP_ID = dbo.N_GROUP.GROUP_ID
LEFT OUTER JOIN
dbo.N_REF_INDUSTRY ON dbo.N_COMPANY.INDUSTRY_ID = dbo.N_REF_INDUSTRY.INDUSTRY_ID
LEFT OUTER JOIN
dbo.N_DIVISION ON dbo.N_DIVISION.DIVISION_ID = dbo.N_CLIENT.DIVISION_ID
LEFT OUTER JOIN
#returnTable ON #returnTable.CLIENT_ID = dbo.N_CLIENT.CLIENT_ID
LEFT OUTER JOIN
dbo.N_BUSINESS_UNIT ON dbo.N_CLIENT.BUSINESS_UNIT_ID = dbo.N_BUSINESS_UNIT.BUSINESS_UNIT_ID
ORDER BY N_Client.client_id
Where upper(title) like '%parameter_value%'
and company_id = 'parameter_value'
and Nmt_practice_code = 'parameter_value' ...............and so on
What we would like to do is to add 15 (where some may be null) input parameters to the definition of the query and then somehow (where the parameter is not null), dynamically add that parameter to the WHERE clause of the query illustrated in italics above. The bold print are examples of 3 of the 15 parameters to be passed into the query by the proc, so basically
title, company_id,Nmt_practice_code would be the 3 parameters being passed into this proc.
So in other words if 9 parameters out of the 15 are passed into the proc, we would like those 9 parameters to be added/built dynamically onto the SQL Query as 9 predicates. I hope I have been clear. Does anyone have any experience with this??? Help!!
Thanks
View 2 Replies
View Related
Feb 18, 2008
Thanks in advance for taking the tiemt o read this post:
I am workingon an application in vb.net 2008 and I have 5 drop down lists on my page.
I have code that worked in .net 2005 for my databind but would like to use new features in 08 to do this same thing.
Here is my 05 code how would I do this same things in 08?
Dim db As New DataIDataContext
Dim GlobalSQLstr As String
GlobalSQLstr = "select Orig_City, ecckt, typeflag, StrippedEcckt, CleanEcckt, ManualEcckt, Switch, Vendor, FP_ID, order_class, Line_type, id from goode2 where 1=1"
If (ddlOrigCity.SelectedValue <> "") Then
GlobalSQLstr &= "and Orig_City = '" & ddlOrigCity.SelectedValue & "'"
End If
If (ddlSwitch.SelectedValue <> "") Then
GlobalSQLstr &= "and switch = '" & ddlSwitch.SelectedValue & "'"
End If
If (ddlType.SelectedValue <> "") Then
GlobalSQLstr &= "and Order_Class = '" & ddlType.SelectedValue & "'"
End If
If (ddlFormatType.SelectedValue <> "9") Then
GlobalSQLstr &= "and typeflag = '" & ddlFormatType.SelectedValue & "'"
End If
If (ddlVendor.SelectedValue <> "") Then
GlobalSQLstr &= "and Vendor = '" & ddlVendor.SelectedValue & "'"
End IfDim AllSearch = From A In db.GoodEcckts2s
If (ddlErrorType.SelectedValue <> "0") Then
GlobalSQLstr &= "and ErrorType = '" & ddlErrorType.SelectedValue & "'"
End IfDim cmd As New SqlClient.SqlCommand
Dim rdr As SqlClient.SqlDataReaderWith cmd.Connection = New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString1").ConnectionString)
.CommandType = Data.CommandType.Text
.CommandText = GlobalSQLstr
.Connection.Open()
rdr = .ExecuteReaderMe.gvResults.DataSource = rdrMe.gvResults.DataBind()
.Connection.Close()
.Dispose()End With
View 4 Replies
View Related
Feb 11, 2008
I am looking for a code where everytime broker name is different, it summarises Broker name and copy/paste those line onto excel spreadsheet. Can any one help me please. Thanks
UW Year
DNo
DN
UW Type
Product
LOB
Class
Broker Name
2005
1
0
F
ONSH FAC
Onshore
MD
R K HARRISON INSURANCE BROKERS LTD
2005
1
0
F
EA I/W FA
EA
NA
WILLIS LTD
2005
1
0
F
EA I/W FA
EA
OU
WILLIS LTD
2006
1
0
F
MAR ENG F
Marine
LF
MILLER INSURANCE SERVICES LTD
2006
1
0
F
MAR ENG F
Marine
LF
MILLER INSURANCE SERVICES LTD
2005
1
0
F
ONSH FAC
Onshore
MD
AON LTD
2007
1
0
F
EA I/W FA
EA
NA
MARSH LTD
2006
1
0
F
ONSH FAC
Onshore
MO
MARSH LTD
2007
1
0
F
ONSH FAC
Onshore
MD
MARSH LTD
2007
1
0
F
ONSH FAC
Onshore
MD
MARSH LTD
View 4 Replies
View Related
Nov 4, 2015
Within a trigger, I'm trying to create a unique table name (using the NEWID()) which I can store the data that is found in the inserted and deleted tables.
Declare @NewID varchar(50) = Replace(convert(Varchar(50),NEWID()),'-','')
Declare @SQLStr varchar(8000)
Set @SQLStr= 'Select * into [TMPIns' + @newID + '] from inserted'
Exec (@SQLStr)
I get the following error: Invalid object name 'inserted'
I know I can do:
Select * into #inserted from inserted
Set @SQLStr= 'Select * into [TMPIns' + @newID + '] from #inserted'
Exec (@SQLStr)
But I don't want to use TempDB as these tables can become big and I also feel that it is redundant. Is there a way to avoid the creation of #inserted?
View 2 Replies
View Related
Aug 29, 2007
Here is the sample query:
DECLARE @TABLENAME NVARCHAR(50);
DECLARE @COL NVARCHAR(50);
DECLARE @VALUE NVARCHAR(50);
/*** THESE VARIABLES WERE ARE BEING DYNAMICALLY FEEDED THROUGH CURSOR***/
DECLARE @SQL1 NVARCHAR(1000);
SET SQL1 = 'SELECT * FROM' + @TABLENAME + ' WHERE' + @ COL + '=" + @VALUE
EXECUTE sp_executesql @SQL1
Now when I execute this SP it gives me error invalid colunm name. I figure out that its because of the variable @VALUE. Eventually I found out that I need to single quote the value of this dynamically feeded variable @ VALUE
is there any way I can do this; give quotes to @value like @ COL + '=" + ' @VALUE'
View 1 Replies
View Related
Dec 11, 2001
Is there a way to 'extend' the use of 'Use DatabaseName' sql statement?
I want to build an dynamic sql string such as
'Use '+ @serverName+ '.Master', but it won't accept serverName. just for database within the same server...
thanks
David
View 2 Replies
View Related
May 22, 2001
I am trying (but so far unable) to build a table with dynamic fields using TSQL.
For example:
SELECT * FROM ACCESSTYPE ORDER BY NAME
returns "A", "B", "C" and "D".
from this recordset I want to create those fields in a table...like this:
CREATE TABLE inclusive {
ID int
...some kind of a loop to add @accessType...
)
...any ideas?
View 3 Replies
View Related
Sep 4, 2007
Here is the query..
@ENTITY, @ FIELD, @KEYID, @VALUE comes dynamically using cursor. Here in this example I have took one sample and assigned it a value to do sanity check.
DECLARE @ENTITY nvarchar (100)
SET @ENTITY ='AccidentDimension'
DECLARE @FIELD nvarchar (100)
SET @FIELD = 'UHReceivedDate'
DECLARE @KEYID nvarchar (100)
SET @KEYID = '1074958'
DECLARE @VALUE varchar (100)
SET @VALUE = '10JAN2020'
DECLARE @FLAG NVARCHAR(50);
SET @FLAG = (SELECT LEFT(@ENTITY, (SELECT CHARINDEX( 'DIM', @ENTITY) -1)) )+ 'LastUpdateFlag';
DECLARE @KeyName NVARCHAR(50);
SET @KeyName = (SELECT LEFT(@ENTITY, (SELECT CHARINDEX( 'DIM', @ENTITY) -1)) )+ 'Key'
DECLARE @KeyValue INT
DECLARE @SQL1 NVARCHAR (1000)
SET @SQL1 = ' SET @KeyValueOUT = Select '+ @KeyName + ' FROM ClaimManagementFact WHERE ClaimKey = ' + @KEYID +
' GROUP BY ' + @KeyName + ' HAVING SUM(TotalClaimCount) > 0 OR SUM(IncidentOnlyClaimCount) > 0 )'
EXECUTE sp_executesql @SQL1, @KeyValueOUT INT OUTPUT;
@KeyValue= @KeyValueOUT OUTPUT;
Select @KeyValue
A) What i want to do is store the value resulting from select statemenet by executing @SQL1 which is INT to @KeyValue. In previous thread I tried various thing but resulting in errors.
Thanks in advance for help
View 6 Replies
View Related
Oct 8, 2007
Hello all
I am trying to update a colunm with the value (Dynamically).
'UPDATE ' + @TABLE + ' SET '+ @FIELD + ' = '''+ @VALUE +'''
well It works fine until @Value contains quotation for instance @value = O'hare Airport
results in termination of the statement because of single quote after O in O'Hare. Is there any way I can see it works
Also suppose if its updating a field which can be say 10 charcters long and when @value has say 15 characters, it terminates. Is there anyway i can avoid this.
View 4 Replies
View Related
Mar 18, 2004
Hi
I have a small problem writing a stored procedure in a SQL Server 2000 database.
I would like to generate som part of the SQL inside this stored procedure that is used in an IN expression of my WHERE clause. There is no problem for me to generate a string containing my expression, the problem is that SQL-Server don´t generate a resulting SQL-statement.
Example:
CREATE PROCEDURE spDynStatement
AS
DECLARE @sPartOfSQLStatement NVARCHAR(100)
-- Some T-SQL that generates the dynamic part of the SQL-statement
-- .
-- .
-- .
-- As substitute I insert the string expression
SET @sPartOfSQLStatement = '''1''' + ', ' + '''1.5'''
-- SELECT @sPartOfSQLStatement results in: '1' , '1.5'
SELECT * FROM BBNOrganization WHERE OrgStructureID IN( @sPartOfSQLStatement ) -- does not work
SELECT * FROM BBNOrganization WHERE OrgStructureID IN( '1', '1.5' ) -- works!!!
GO
Thankfull for ideas on how to solve my problem,
Peter
View 1 Replies
View Related
Nov 15, 2005
The dynamic SQL statements with output parameters, unfortunately, in the documentation are not described.
À) Simple example of the dynamic SQL statement with output parameters
-- dynamic SQL statements expects parameter of type 'ntext/nchar/nvarchar'.
declare @SQLString nvarchar(4000), @ParmDefinition nvarchar(4000)
-- the third parameter passed in the dynamic statement as by output, returns the length of
-- the hypotenuses of a right triangle, two first parameters are lengths of legs of a triangle
declare @nHypotenuse float
select @SQLString = 'select @nHypotenuse = sqrt(square(@nLeg1_of_a_triangle)+square(@nLeg2_of_a_triangle))',
@ParmDefinition = '@nLeg1_of_a_triangle float, @nLeg2_of_a_triangle float, @nHypotenuse float out'
-- we call the dynamic statement in such a way
exec sp_executesql @SQLString, @ParmDefinition, @nLeg1_of_a_triangle = 3.0, @nLeg2_of_a_triangle = 4.0, @nHypotenuse = @nHypotenuse out
-- or in such a way
exec sp_executesql @SQLString, @ParmDefinition, 3.0, 4.0, @nHypotenuse out
select @nHypotenuse -- Displays 5.0
B) Example of usage of the dynamic statement with output parameter and the function GETALLWORDS.
The following stored procedure get all words from a field of the type text or ntext, the word length should not exceed 4000 characters.
CREATE PROCEDURE SP_GETALLWORDSFROMTEXT
@TableName sysname, @FieldIdName sysname, @FieldIdValue sql_variant, @FieldTextName sysname, @cDelimiters nvarchar(256) = NULL
AS
-- this Stored procedure inserts the words from a text field into the table.
-- WORDNUM int – Sequence number of a word
-- WORD nvarchar(4000) – the word
-- STARTOFWORD int – position in the text field, with which the word starts
-- LENGTHOFWORD smallint – length of the word
-- Parameters
-- @TableName name of the table with the text or ntext field
-- @FieldIdName name of Id field
-- @FieldIdValue value of Id field
-- @FieldTextName name of field text or ntext
-- @cDelimiters Specifies one or more optional characters used to separate words in the text field
begin
set nocount on
declare @k int, @wordcount int, @nBegSubString int, @nEndSubString int, @nEndString int, @divisor tinyint, @flag bit, @RetTable bit,
@cString nvarchar(4000), @TypeField varchar(13), @SQLString nvarchar(4000), @ParmDefinition nvarchar(500), @nBegSubString1 smallint, @nEndSubString1 smallint
select @TableName = object_name(object_id(lower(ltrim(rtrim(@TableName))))), @FieldIdName = lower(ltrim(rtrim(@FieldIdName))), @FieldTextName = lower(ltrim(rtrim(@FieldTextName))),
@cDelimiters = isnull(@cDelimiters, nchar(32)+nchar(9)+nchar(10)+nchar(13)), -- if no break string is specified, the function uses spaces, tabs, carriage return and line feed to delimit words.
@nBegSubString = 1, @nEndSubString = 4000, @flag = 0, @RetTable = 0, @wordcount = 0
-- If the temporary table is not created in the calling procedure, we create the temporary table
if object_id( 'tempdb..#GETALLWORDSFROMTEXT') is null
begin
create table #GETALLWORDSFROMTEXT (WORDNUM int, WORD nvarchar(4000), STARTOFWORD int, LENGTHOFWORD smallint)
select @RetTable = 1
end
-- we use the dynamic SQL statement to receive the exact name of text field
-- as we can write names of fields by a call of the given stored procedure in the arbitrary register
-- in the string of parameters definition @ParmDefinition we use a keyword output
-- and by a call of the dynamic SQL statement exec sp_executesql @SQLString, @ParmDefinition, @FieldTextName = @FieldTextName output
-- Also we use a keyword output for definite before parameter
select @SQLString = 'select @FieldTextName = name from syscolumns where id = OBJECT_ID('''+ @TableName+''') and lower(name) = '''+@FieldTextName+''''
select @ParmDefinition = '@FieldTextName sysname output'
exec sp_executesql @SQLString, @ParmDefinition, @FieldTextName = @FieldTextName output
-- we use the dynamic SQL statement to receive the exact name of Id field
select @SQLString = 'select @FieldIdName = name from syscolumns where id = OBJECT_ID('''+ @TableName+''') and lower(name) = '''+@FieldIdName+''''
select @ParmDefinition = '@FieldIdName sysname output'
exec sp_executesql @SQLString, @ParmDefinition, @FieldIdName = @FieldIdName output
-- we use the dynamic SQL statement to receive the type of field (text or ntext)
select @SQLString = 'select @TypeField = name from systypes where xtype = any ( select xtype from syscolumns where id = OBJECT_ID('''+ @TableName+''') and lower(name) = '''+@FieldTextName+''')'
select @ParmDefinition = '@TypeField varchar(13) output'
exec sp_executesql @SQLString, @ParmDefinition, @TypeField = @TypeField output
select @divisor = case @TypeField when 'ntext' then 2 else 1 end -- 2 for unicode
-- we use the dynamic SQL statement to receive a length of the text field
select @SQLString = 'select @nEndString = 1 + datalength('+ @FieldTextName+')/'+cast( @divisor as nchar(1)) +' from '+@TableName +' where '+ @FieldIdName+' = ' +cast(@FieldIdValue as nchar(50))
select @ParmDefinition = '@nEndString int output'
exec sp_executesql @SQLString, @ParmDefinition, @nEndString = @nEndString output
-- We cut the text field into substrings of length no more than 4000 characters and we work with substrings in cycle
while 1 > 0
begin
-- we use the dynamic SQL statement to receive a substring of a type nvarchar(4000) from text field
select @SQLString = 'select @cString = substring('+ @FieldTextName+','+cast( @nBegSubString as nvarchar(20)) +',' +
cast( @nEndSubString - @nBegSubString + 1 as nvarchar(20))+') from '+@TableName +' where '+ @FieldIdName+' = ' +cast(@FieldIdValue as nchar(50))
select @ParmDefinition = '@cString nvarchar(4000) output'
exec sp_executesql @SQLString, @ParmDefinition, @cString = @cString output
select @nBegSubString1 = 1, @nEndSubString1 = @nEndSubString - @nBegSubString +1
while charindex(substring(@cString, @nBegSubString1, 1) COLLATE Latin1_General_BIN, @cDelimiters COLLATE Latin1_General_BIN) > 0 and @nEndSubString >=@nBegSubString -- skip the character not in word, if any
select @nBegSubString = @nBegSubString + 1 , @nBegSubString1 = @nBegSubString1 + 1
while charindex(substring(@cString, @nEndSubString1, 1) COLLATE Latin1_General_BIN, @cDelimiters COLLATE Latin1_General_BIN) = 0 and @nEndSubString >=@nBegSubString -- skip the character in word, if any
select @nEndSubString = @nEndSubString - 1, @nEndSubString1 = @nEndSubString1 - 1
if @nEndSubString >=@nBegSubString
begin
select top 1 @wordcount = WORDNUM from #GETALLWORDSFROMTEXT order by WORDNUM desc
select @cString = substring(@cString, @nBegSubString1, @nEndSubString1-@nBegSubString1+1)
-- we use a function GETALLWORDS which one works with strings of a type nvarchar(4000)
-- we add outcome result in the temporary table
insert into #GETALLWORDSFROMTEXT (WORDNUM, WORD, STARTOFWORD, LENGTHOFWORD)
select (@wordcount+WORDNUM), WORD, (@nBegSubString+STARTOFWORD-1), LENGTHOFWORD from dbo.GETALLWORDS(@cString, @cDelimiters)
select @nBegSubString = @nEndSubString + 1, @nEndSubString = @nEndSubString + 4000
end
else
select @nEndSubString = @nEndSubString + 4000 -- In a case if the substring consists of one delimiter
if @flag = 1
break
if @nEndString <= @nEndSubString
select @flag = 1, @nEndSubString = @nEndString
end
-- If in a calling procedure the table was not created, we show the result
if @RetTable = 1
select * from #GETALLWORDSFROMTEXT
end
GO
Example of the call Stored procedure SP_GETALLWORDSFROMTEXT
declare @cDelimiters nvarchar(256)
select @cDelimiters = '"-,.:!?«»()'+SPACE(1)+CHAR(9)+CHAR(10)+CHAR(13)+CHAR(12)
if object_id( 'tempdb..#GETALLWORDSFROMTEXT') is not null
drop table #GETALLWORDSFROMTEXT
create table #GETALLWORDSFROMTEXT (WORDNUM int, WORD nvarchar(4000), STARTOFWORD int, LENGTHOFWORD smallint)
exec dbo.SP_GETALLWORDSFROMTEXT 'Your Table name', 'Your Id field name', Value of Id field, ' text or ntext field name', @cDelimiters
if object_id( 'tempdb..#GETALLWORDSFROMTEXT') is not null
select * from #GETALLWORDSFROMTEXT
-- GETALLWORDS() User-Defined Function Inserts the words from a string into the table.
-- GETALLWORDS(@cString[, @cDelimiters])
-- Parameters
-- @cString nvarchar(4000) - Specifies the string whose words will be inserted into the table @GETALLWORDS.
-- @cDelimiters nvarchar(256) - Optional. Specifies one or more optional characters used to separate words in @cString.
-- The default delimiters are space, tab, carriage return, and line feed. Note that GETALLWORDS( ) uses each of the characters in @cDelimiters as individual delimiters, not the entire string as a single delimiter.
-- Return Value table
-- Remarks GETALLWORDS() by default assumes that words are delimited by spaces or tabs. If you specify another character as delimiter, this function ignores spaces and tabs and uses only the specified character.
-- Example
-- declare @cString nvarchar(4000)
-- set @cString = 'The default delimiters are space, tab, carriage return, and line feed. If you specify another character as delimiter, this function ignores spaces and tabs and uses only the specified character.'
-- select * from dbo.GETALLWORDS(@cString, default)
-- select * from dbo.GETALLWORDS(@cString, ' ,.')
-- See Also GETWORDNUM() , GETWORDCOUNT() User-Defined Functions
CREATE function GETALLWORDS (@cString nvarchar(4000), @cDelimiters nvarchar(256))
returns @GETALLWORDS table (WORDNUM smallint, WORD nvarchar(4000), STARTOFWORD smallint, LENGTHOFWORD smallint)
begin
declare @k smallint, @wordcount smallint, @nEndString smallint, @BegOfWord smallint, @flag bit
select @k = 1, @wordcount = 1, @BegOfWord = 1, @flag = 0, @cString = isnull(@cString, ''),
@cDelimiters = isnull(@cDelimiters, nchar(32)+nchar(9)+nchar(10)+nchar(13)), -- if no break string is specified, the function uses spaces, tabs, carriage return and line feed to delimit words.
@nEndString = 1 + datalength(@cString) /(case SQL_VARIANT_PROPERTY(@cString,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode
while 1 > 0
begin
if @k - @BegOfWord > 0
begin
insert into @GETALLWORDS (WORDNUM, WORD, STARTOFWORD, LENGTHOFWORD) values( @wordcount, substring(@cString, @BegOfWord, @k-@BegOfWord), @BegOfWord, @k-@BegOfWord ) -- previous word
select @wordcount = @wordcount + 1, @BegOfWord = @k
end
if @flag = 1
break
while charindex(substring(@cString, @k, 1) COLLATE Latin1_General_BIN, @cDelimiters COLLATE Latin1_General_BIN) > 0 and @nEndString > @k -- skip break characters, if any
select @k = @k + 1, @BegOfWord = @BegOfWord + 1
while charindex(substring(@cString, @k, 1) COLLATE Latin1_General_BIN, @cDelimiters COLLATE Latin1_General_BIN) = 0 and @nEndString > @k -- skip the character in the word
select @k = @k + 1
if @k >= @nEndString
select @flag = 1
end
return
end
For more information about string UDFs Transact-SQL please visit the
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115
View 3 Replies
View Related
May 1, 2007
Hi,I'm using a 3rd-party app's back end which stores SQL statements in atable, so I have no choice but to use dynamic SQL to call them (unlesssomeone else knows a workaround...)Problem is, I can't get the statement to run properly, and I can't seewhy. If I execute even a hard-coded variation likeDECLARE @sql nvarchar(MAX)SET @sql ='SELECT foo FROM foostable'sp_executesql @sqlI get: Incorrect syntax near 'sp_executesql'.If I runsp_executesql 'SELECT foo FROM foostable'I get: Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.which I understand, as it's omitting the N converter--so if I runsp_executesql N'SELECT foo FROM foostable'it's fine. I don't understand why the first version fails. Is it somesort of implicit conversion downgrading @sql? Every variation of CASTand CONVERT I use has no effect.This is SQL Server 2005 SP2. Thanks in advance.
View 11 Replies
View Related
Aug 30, 2007
Hello all:
Here is a sample query:
DECLARE @KEYID NVARCHAR (50) ; SET @KEYID = '1074958'
DECLARE @ENTITY NVARCHAR (100); SET @ENTITY = 'HouseDimension'
DECLARE @KeyCol NVARCHAR(50);
SET @KeyCol = (SELECT LEFT(@ENTITY, (SELECT CHARINDEX( 'DIM', @ENTITY) -1)) )+ 'Key'
DECLARE @KeyValue NVARCHAR (1000)
SET @KeyValue = 'SELECT '+ @KeyCol + ' FROM HouseManagementFact WHERE HouseKey = ' + @KEYID +
' GROUP BY ' + @KeyCol + ' HAVING SUM(TotalClaimCount) > 0 OR SUM(HouseCount) > 0 '
The value resulting from Executing @KeyValue is an integer.
I want to store this value in a new variable say @VAR2
When I do this
DECLARE @VAR2 INT
SET @VAR2 = execute sp_executesql @KeyValue
its giving me an error.
can somebody let me know the correct form of storing the value resulting from @KeyValue in some variable ?
View 3 Replies
View Related
Aug 23, 2005
I have a couple of servers each with around 900 databasesa and need to do indexdeframentation on them.
The loop to database and loop for tables are fine. The problem is the loop for indexes and always return -1 for @@Fetch_status after open the cur_indfetch cursor as follows
select @sqlStringField = ' DECLARE cur_indfetch CURSOR FOR ' + ' SELECT indid FROM ' + @databaseName + '.dbo.sysindexes ' + ' WHERE id = OBJECT_ID (' + '''' + @TableName + ''''+ ') and keycnt > 0'
print @sqlStringField
exec master..sp_executesql @sqlStringField
OPEN cur_indfetch
FETCH NEXT FROM cur_indfetch INTO @indid
print ' before while @@FETCH_STATUS: ' + cast(@@FETCH_STATUS as varchar(5)) + ' @indid:' + cast(isnull(@indid, 0) as varchar(5))
WHILE @@FETCH_STATUS = 0
BEGIN
print ' Cursor 3--cur_indfetch--' + @databaseName + '--@tableName:' + @tableName + '--' + cast(@indid as varchar(7))
IF @indid <> 255
begin
DBCC INDEXDEFRAG (@databaseName, @TableName, @indid)
end
FETCH NEXT FROM cur_indfetch INTO @indid
END
CLOSE cur_indfetch
DEALLOCATE cur_indfetch
any idea?
thanks
David
View 3 Replies
View Related
Nov 18, 2011
@strSql is a dynamic query in while loop which can return single record, single row of records, multiple rows of records.
So only if it returns single record then I have to store it otherwise convert to xml before storing.
1) If it returns 1 record(1row and 1 column) then save as it is.
2) if it returns a row with more than 1 columns then convert to xml before saving.
2) if it returns data rows then convert to xml before saving.
View 2 Replies
View Related
Jan 6, 2004
thanks for reading.
i'm interested in improving the format of this query. consider me clueless today, if you will. :) how can i fix this to make it dynamically move over the years? is there something i can do with set manipulation that is smarter than this?
the goal of this query is to return cases per year, where "year" is defined as (Oct 1, YYYY - Sep 30, YYYY+1) instead of the typical YYYY
problem is, i have to write it as some cludgy dynamic sql looping over an incremented year. i don't know of any other way.
again, thanks for reading ... and any help in advance.
SELECTcount(*) as 'Data Points', '2001' as 'Experiment Year'
FROM tbl_experiment_data
WHEREstart_date BETWEEN '9/30/2001' AND '10/01/2002'
and completion_date BETWEEN '9/30/2001' AND '10/01/2002'
and status = 'CaseClosed'
UNION
SELECTcount(*) as 'Data Points', '2002' as 'Experiment Year'
FROM tbl_experiment_data
WHEREstart_date BETWEEN '9/30/2002' AND '10/01/2003'
and completion_date BETWEEN '9/30/2002' AND '10/01/2003'
and status = 'CaseClosed'
UNION
...
expected output....
Data Points______ Experiment Year
32_____________ 2001
102____________ 2002
.... ....
View 4 Replies
View Related
Apr 4, 2006
Hello-
I'm trying to read a table that has database connection information for other DBs and use this within an "Execute SL Task" task. I have seen a number of posts that talk about this possibility, but I have not been able to get it to work yet.
When I've tried to set the connection to a variable (@[User::DB_ConnectionStr]) in the Expressions area of the SQL Task, the Connection type defaults to OLE DB and I can't seem to force it back to ADO or ADO.net.
I've tried doing this with the variable being set to both a connection object and a String with the connection string, but neither seems to work.
Any suggestions? Should this be a string value of the connection string? Am I missing something when trying to set the connection type?
Thanks much!
Tristan
View 3 Replies
View Related
Nov 19, 2007
Can anyone please give me the equivalent tsql for sql server 2000 for the following two queries which works fine in sql server 2005
1
-- Full Table Structure
select t.object_id, t.name as 'tablename', c.name as 'columnname', y.name as 'typename', case y.namewhen 'varchar' then convert(varchar, c.max_length)when 'decimal' then convert(varchar, c.precision) + ', ' + convert(varchar, c.scale)else ''end attrib,y.*from sys.tables t, sys.columns c, sys.types ywhere t.object_id = c.object_idand t.name not in ('sysdiagrams')and c.system_type_id = y.system_type_idand c.system_type_id = y.user_type_idorder by t.name, c.column_id
2
-- PK and Index
select t.name as 'tablename', i.name as 'indexname', c.name as 'columnname' , i.is_unique, i.is_primary_key, ic.is_descending_keyfrom sys.indexes i, sys.tables t, sys.index_columns ic, sys.columns cwhere t.object_id = i.object_idand t.object_id = ic.object_idand t.object_id = c.object_idand i.index_id = ic.index_idand c.column_id = ic.column_idand t.name not in ('sysdiagrams')order by t.name, i.index_id, ic.index_column_id
This sql is extracting some sort of the information about the structure of the sql server database[2005]
I need a sql whihc will return the same result for sql server 2000
View 1 Replies
View Related
May 28, 2010
difference between FOR LOOP and FOREACH LOOP with example(if possible) in SSIS.
View 4 Replies
View Related
Feb 22, 2006
I have a table with RowID(identity). I need to loop though the table using RowID(not using a cursor). Please help me.
Thanks
View 6 Replies
View Related
Mar 3, 2006
I have a foreach loop that is supposed to loop through a recordset, however it doesn't loop. It just repeats the same row, row after row.
I would like to look into the recordset variable but I can't because it is a COM object and the ADODB namespace is not available in the script task.
Any solution to this? anyone experienced anything similar
View 1 Replies
View Related
Jul 8, 2006
I have a table called Tbltimes in an access database that consists of the following fields:
empnum, empname, Tin, Tout, Thrs
what I would like to do is populate a grid view the a select statement that does the following.
display each empname and empnum in a gridview returning only unique values. this part is easy enough. in addition to these values i would also like to count up all the Thrs for each empname and display that sum in the gridview as well. Below is a little better picture of what I€™m trying to accomplish.
Tbltimes
|empnum | empname | Tin | Tout | Thrs |
| 1 | john | 2:00PM | 3:00PM |1hr |
| 1 | john | 2:00PM | 3:00PM | 1hr |
| 2 | joe | 1:00PM | 6:00PM | 5hr |
GridView1
| 1 | John | 2hrs |
| 2 | Joe | 5hrs |
im using VWD 2005 for this project and im at a loss as to how to accomplish these results. if someone could just point me in the right direction i could find some material and do the reading.
View 18 Replies
View Related
Feb 1, 2007
Hi! Hello. I have now started to build my own community. And I have some questions on the database.For the users to login I use the login control and all the users information is stored in the ASPNETDB.MDF database.In the web.config file I have created some profiles for saving some information about the users (Name, Birth, Town) and so on.Now. All the users in this community will have their own profile page, Guestbooks ++.So I was wondering if I should create tables for all features like guestbook, profile pages or should I do this by using Profile (ASP.NET).How many users does ASPNETDB support?.
View 1 Replies
View Related
Jul 31, 2007
Is there any sql method that takes 3 parameter like, day, month and year . And return me the date.
For example
function(10,3,2007) and it returns 10-03-2007
Thanks,
View 2 Replies
View Related
