SELECT 'abc' + '-' + CAST(SeqNo AS VARCHAR) + '-' + CAST(Payment AS VARCHAR) FROM abc WHERE SeqNo = 1
SELECT 'def' + '-' + CAST(SeqNo AS VARCHAR) + '-' + CAST(Payment AS VARCHAR) FROM def WHERE SeqNo = 1
As you can see since 1 doesn't exists in table 'def' nothing is returned as expected. However, if a row isn't returned I want to be able to enter my own row such as
I have a function that accespts a string and a delimeter returns the results in a temp table. I am using the funtion for one of the columns in my view that needs be to split and display the column into different columns. The view takes for ever to run and finally it doesn't split and doesn't display in the column.
Function: ----------------------------------- ALTER FUNCTION [dbo].[func_Split] ( @DelimitedString varchar(8000),
Not sure what I am missing in the above view why it doesn't split the string.
I'm trying to use a UDF that returns a table, but I'm not sure of the syntax to invoke it. I've found examples in BOL and on-line like the following:
SELECT * FROM dbo.fn_MyTableFunc( 123.09, 'MyID' )
But I need the input parameter to be obtained from another table. For a very simplistic example, I've got 4 tables (and yes, I know that I can get the results I want for this example without using a UDF, but humor me):
CREATE TABLE tUser (UserID int PRIMARY KEY, UserName varchar(50)) CREATE TABLE tAcctGroup (AcctGroupID int PRIMARY KEY, AcctGroupName varchar(50)) CREATE TABLE tAcct (AcctID int PRIMARY KEY, AcctGroupID int, AcctName varchar(50)) CREATE TABLE tMapUserToGroup (UserID int, AcctGroupID int) GO
INSERT INTO tUser VALUES (111, 'Me')
INSERT INTO tAcctGroup VALUES (1, 'NY') INSERT INTO tAcct VALUES (11, 1, 'New York City') INSERT INTO tAcct VALUES (12, 1, 'Syracuse')
INSERT INTO tAcctGroup VALUES (2, 'GA') INSERT INTO tAcct VALUES (21, 2, 'Atlanta') INSERT INTO tAcct VALUES (22, 2, 'Savannah') INSERT INTO tAcct VALUES (23, 2, 'Augusta')
INSERT INTO tAcctGroup VALUES (3, 'TX') INSERT INTO tAcct VALUES (31, 3, 'Dallas') INSERT INTO tAcct VALUES (32, 3, 'Houston') INSERT INTO tAcct VALUES (33, 3, 'El Paso') INSERT INTO tAcct VALUES (34, 3, 'San Antonio')
INSERT INTO tAcctGroup VALUES (4, 'CA') INSERT INTO tAcct VALUES (41, 4, 'Los Angeles') INSERT INTO tAcct VALUES (42, 4, 'San Francisco')
INSERT INTO tMapUserToGroup VALUES (111,2) INSERT INTO tMapUserToGroup VALUES (111,4) GO
CREATE FUNCTION dbo.ufnGetAcctList(@AcctGroupID int) RETURNS @tAcct table (AcctID int, AcctName varchar(50)) AS BEGIN INSERT INTO @tAcct SELECT AcctID, AcctName FROM tAcct WHERE AcctGroupID = @AcctGroupID RETURN END GO
I know that I can do: SELECT * FROM TestDB.dbo.ufnGetAcctList(4)
But I want the equivalent of: SELECT AcctID, AcctName FROM tAcct WHERE AcctGroupID IN (SELECT AcctGroupID FROM tMapUserToGroup WHERE UserID = 111)
Which uses tMapUserToGroup to obtain the AcctGroupID to pass into the function. The results would be: AcctID AcctName ----------------------------- 21 Atlanta 22 Savannah 23 Augusta 41 Los Angeles 42 San Francisco
Any thoughts? Thanks in advance for your help. Cat
I have a requirement of creating nested tables in SQL server. how to create them. Just to give a background I am trying to move the RDBMS from oracle to SQL server.
Structure of tables is as follows. I have table 'Employees' with address as one of the column. I have one more table with columns Street, Town, Dist, State. When I query the table 'Employees' I should see the attribute name and values of all the columns in address table in address column.
Employees: with columns: ID, FirstName, LastName, dept, gender, dob, address
Address (Nested table): with columns : Street, Town, Dist, State
This was done in oracle using Nested tables and user defined data types. what is alternative for this in SQL server. How can I achive this requirement in SQL server.
I have 3 fields in my table say (F1, F2, F3). I want to get the max value out of the three fields for each row. I can create a user-defined function which accepts 3 arguments and then return the max value if i am using SQL Server 2000. But now i am using only SQL Server 7.0 (it does not support user-defined functions :confused: )
So any one could kindly let me know how could i do it in SQL Server 7.0
Hi all!!Does anybody know how I can create a function in SQL 7.0?? I have tocreate functions that return a value that can be used in a selectstatement. I think SQL Server version 7.0 doesn't support CREATEFUNCTION, does it?Ex:Select MyFunction(Parameter)From MyTableThanks a lot,
Dear all I wants to run sql server user defined function when linked two server.
I have linked two sql server.There is one function called getenc().This function created on first server.What i want.I wants to run this user defined function on the second sql server. can any one help me?
This is my problem: I do not know how to get the servername from a C# user defined function . Is this possible?
I am writing a User Defined Function (UDF). Inside of this user defined function I need the name of the databaseserver that it is running on. Does anyone have an idea how I might do this? Is there an enviromental variable that I could access within the C# code I use to write the UDF?
I could always use a parameter to pass in the name of the server, but I would like to have as few parameters as possible.
EXEC master.dbo.xp_msver ProductVersion can be used to return the server version in a resultset. I need this to do some conditional coding between varchar and varchar(max) in a UDF, so size of the text I return must be different between the SQL2000 and SQL2005.
I cant call an xp_ that returns a resultset within a UDF can I, so how can I get the SQL version?
Hi, i defined a sqldatasource in VWD manually (option specify sql statement) because several tables are involved. I also need a Delete statement, so i defined it also manually. The select and delete statement are : <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:test %>" SelectCommand="SELECT aspnet_Users.UserName as lid, aspnet_Roles.RoleName as categorie, aspnet_Users.beheerder as beheerder, aspnet_Membership.Email as emailadres FROM aspnet_Users INNER JOIN aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId INNER JOIN aspnet_UsersInRoles ON aspnet_Users.UserId = aspnet_UsersInRoles.UserId inner JOIN aspnet_Roles ON aspnet_UsersInRoles.RoleId = aspnet_Roles.RoleId order by username" DeleteCommand="delete from aspnet_users where userid=@userid"> <DeleteParameters> <asp:Parameter Name="userid" /> </DeleteParameters> </asp:SqlDataSource> My problem is that the Select works, but not the Delete. Any idea why? Thanks tartuffe
I have a User Defined Function in my SQL Server 2000 database which takes a string and adds an integer quantity to it. The function basically takes the string (string because of first character and spaces) and adds an integer to it creating a new number (starting number and ending number concept).If I pass in a string that has a letter for the first character, it works fine. However, if the first character is an integer it trims out all 0s and whitespace and ruins the necessary formatting. Note, the formatting is always the same - x xxx xxxx xx. Any help or ideas would be appreciated.Example:D 499 8900 01 plus a Quantity of 10 returns D 499 89000 11 which is perfect.However,0 076 0000 03 plus a Quantity of 1 returns 764 764 (it should be 0 076 0000 04)------------------------------------CREATE FUNCTION [dbo].[NEW_End_NR2]( @OldStr as char(20), @Quantity int )RETURNS @NewNR_Tbl TABLE (New_EndNr char(20) primary key)AS BEGIN DECLARE @NewStr char(20) DECLARE @addNr integerBEGIN Set @addNr = @Quantity set @NewStr = (select REPLACE(@OldStr,left(@OldStr, 1),'')) set @NewStr = (select REPLACE(@NewStr,' ', '')) + @addNr set @NewStr = (SELECT left(@OldStr, 1) + SPACE(1) + left(@NewStr,3) + SPACE(1) + left(REPLACE(@NewStr, left(@NewStr,3), ''), 4) + SPACE(1) + left(REPLACE(@NewStr, left(@NewStr,7), ''), 3)) BEGIN INSERT INTO @NewNR_Tbl (New_EndNr) VALUES(@NewStr) END END RETURNEND
I created a user-defined datatype in Microsoft SQL server using the Enterprise Manager. But, I am not able to find options to edit this data type. There are options to delete but not for editing an existing user defined data type.
Can any one help me how to edit this user defined datatype ?
Also, are there any better ways to create and manage user defined data types in MS SQL ?
I'm trying to create a simple function that will do a count on a table. I want to pass the table name in form of a parameter to the variable and this function will return the count as an int. See my function below...
CREATE FUNCTION count_rows (@tablename varchar(100) RETURNS int AS BEGIN DECLARE @emp_count AS int declare @declaration varchar(100)
The errors I am getting are as follows:
Msg 102, Level 15, State 1, Procedure count_rows, Line 3 Incorrect syntax near 'RETURNS'. Msg 102, Level 15, State 1, Procedure count_rows, Line 10 Incorrect syntax near '@declaration'. Msg 178, Level 15, State 1, Procedure count_rows, Line 14
A RETURN statement with a return value cannot be used in this context.
Our server has integrated security setup, upon startup of the server, we have a continuous flow of error msg: Login Failed: Reason: User '_' not defined as a valid user of trusted connection. The timing of these messages only around 30 seconds apart The only incident from Technet I can find is Q186314, but we don't have replication setup, anyone knows where I can look into ? I recycle the server but didn't help. I appreciate any help I can get .. Thanks.
User-Defined string Functions MS SQL Server 2005 Transact-SQL SQLCLR (VB. Net, C#.Net, C++. Net)
Ladies and Gentlemen,
I am pleased to offer, free of charge, the following string functions MS SQL Server 2005 Transact-SQL SQLCLR (VB. Net, C#.Net, C++. Net):
AT(): Returns the beginning numeric position of the nth occurrence of a character expression within another character expression, counting from the leftmost character. RAT(): Returns the numeric position of the last (rightmost) occurrence of a character string within another character string. OCCURS(): Returns the number of times a character expression occurs within another character expression (including overlaps). OCCURS2(): Returns the number of times a character expression occurs within another character expression (excluding overlaps). PADL(): Returns a string from an expression, padded with spaces or characters to a specified length on the left side. PADR(): Returns a string from an expression, padded with spaces or characters to a specified length on the right side. PADC(): Returns a string from an expression, padded with spaces or characters to a specified length on the both sides. CHRTRAN(): Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression. STRTRAN(): Searches a character expression for occurrences of a second character expression, and then replaces each occurrence with a third character expression. Unlike a built-in function Replace, STRTRAN has three additional parameters. STRFILTER(): Removes all characters from a string except those specified. GETWORDCOUNT(): Counts the words in a string. GETWORDNUM(): Returns a specified word from a string. GETALLWORDS(): Inserts the words from a string into the table. PROPER(): Returns from a character expression a string capitalized as appropriate for proper names. RCHARINDEX(): Similar to the Transact-SQL function Charindex, with a Right search. ARABTOROMAN(): Returns the character Roman numeral equivalent of a specified numeric expression (from 1 to 3999). ROMANTOARAB(): Returns the number equivalent of a specified character Roman numeral expression (from I to MMMCMXCIX).
AT, PADL, PADR, CHRTRAN, PROPER: Similar to the Oracle functions PL/SQL INSTR, LPAD, RPAD, TRANSLATE, INITCAP.
Plus, there are CHM files in English, French, Spanish, German and Russian.
Plus, there are versions for MS SQL SERVER, SYBASE ASA, DB2, Oracle.
More than 8000 people have already downloaded my functions. I hope you will find them useful as well.
For more information about string UDFs MS SQL Server 2005 Transact-SQL SQLCLR (VB. Net, C#.Net, C++. Net) please visit the http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,29527
Please, download the file http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,2,29527
So, I've got this query running and it works great providing there is a record in both DataBases. Now, I need to get all of those that have a record in DBServer1 but not in TranscendDB. I assume i'd use an If not exists, but can't figure out the syntax when using the Linked Object...
select Portfolio from [TranscendDB].[dbo].[CMContactEvents] as CM inner join ( select N.SSN, A.ACCOUNTNUMBER from [DBServer1].[DB1].[dbo].[Account] AS A,
I Have a nifty little stored procedure that takes data from an ASP form post and inserts the data into two tables. However what I really need this to do before the insert is check whether there is already a record that matches some of the criteria, and if so returns an error "This Username already exists" and only if there isn't a record that matches the criteria is the record inserted.
my current stored procedure looks like this -
Code Snippet@siteid int,@companyname nvarchar(50),@address nvarchar(500),@phone nvarchar(50),@fax nvarchar(50),@email nvarchar(225),@url nvarchar(225),@companytype nvarchar(50),@billingcontact nvarchar(50),@name nvarchar(50),@AccountType nvarchar(50),@PASSWORD nvarchar(50),@AccountLive nvarchar(50),@EmployeeLevel nvarchar(50)ASDeclare @NewID INTINSERT INTO dbo.JBClient(JBCLSiteID, JBCLName, JBCLAddress, JBCLPhone, JBCLFax, JBCLEmail, JBCLCompanyType, JBCLURL, JBCLAccountType, JBCLAccountlive, JBCLBillingContact)VALUES (@siteid, @companyname, @address, @phone, @fax, @email, @companytype, @url, @AccountType, @AccountLive, @billingcontact)SELECT @NewID = SCOPE_IDENTITY()INSERT INTO dbo.JBEmployee(JBEClientID, JBESiteID, JBEName, JBELevel, JBEUsername, JBEPassword, JBEAddress, JBEPhone)VALUES (@NewID, @siteid, @name, @EmployeeLevel, @email, @PASSWORD, @address, @phone) The values that i need to check against are -
in the table dbo.JBEmployee against columns JBESiteID & JBEUsername
What i would really like to do if a record exists is return the user to an ASP page, which contains all of the variables previously enterred -,
I frequently use the "If NOT EXISTS (Select ...) Insert ..." statement and would like to display a message to the user after the execution of the statement to say whether the Insert was or wasn't executed. Is there a way to get SQL to return a value that will tell me if the Insert was carried out or not? If not, perhaps you know of some other efficient way of handling this situation.