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.
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.
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!
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),
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
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...
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 )
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 '
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 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
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
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.
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'
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...
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.
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.
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.
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'''
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
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
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
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.
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
@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.
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 .... ....
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?
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
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.
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?.
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
