SQL Server 2012 :: How To Return User-defined Row When A Record Doesn't Exists

Dec 29, 2014

What I want to do is return a row of data when my query doesn't return a record. I have two tables:

CREATE TABLE dbo.abc(
SeqNo smallint NULL,
Payment decimal(10, 2) NULL
) ON PRIMARY

[Code] ....

So when I run the following query:

SELECT 'abc' + '-' + CAST(SeqNo AS VARCHAR) + '-' + CAST(Payment AS VARCHAR) FROM abc WHERE SeqNo = 1
UNION
SELECT 'def' + '-' + CAST(SeqNo AS VARCHAR) + '-' + CAST(Payment AS VARCHAR) FROM def WHERE SeqNo = 1
abc-1-200.00
abc-1-500.00

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

abc-1-200.00
abc-1-500.00
def-0-0.00

View 4 Replies


ADVERTISEMENT

SQL Server 2012 :: Using User Defined Function In View?

Jan 31, 2014

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),

[Code].....

Not sure what I am missing in the above view why it doesn't split the string.

View 8 Replies View Related

SQL Server 2012 :: Convert Stored Procedure To User Defined Function?

Feb 23, 2015

I have created a store procedure, but the requirement is function because by using this function we need to add columns in a table with SSIS.

I have tried to create function, but the error I am facing is select statement can not return data.

CREATE PROCEDURE SP_STAT_CURR
(
@I_NET_AMOUNT NUMERIC(10,3),
@I_DOCUMENT_CURR VARCHAR(3),
@I_TARGET_CURR VARCHAR(3)

[code]....

View 9 Replies View Related

SQL Server 2012 :: User Defined Functions - Include Comments In Parameter Intellisense?

Aug 22, 2014

If you use the LEFT() function for example it provides intellisense support for some of the required parameters.

Is there a way to get that same descriptive text in user defined functions?

Some comment block you define when creating the function?

View 4 Replies View Related

User-Defined Func Syntax For Table Return

Oct 30, 2006

Hi all!

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

View 7 Replies View Related

Insert Automatically Many Record For User Defined Range

Jul 11, 2012

I want insert automatically records for Range that user definded

For example user define :

from to city
10000 12000 tehran
15000 19000 babol

I should inserted 2000 record for tehran (10000,10001,....,12000) and 4000 record for babol (15000,15001,...,19000)

View 3 Replies View Related

SQL 2012 :: Nested Tables And User Defined Data Types?

Jun 5, 2015

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.

View 2 Replies View Related

SQL 2012 :: NET Framework Error Occurred During Execution Of User-defined Routine Or Aggregate

Aug 4, 2015

when i try to deploy a packages in integration service catalog i am getting the below error

.NET Framework error occurred during execution of user-defined routine or aggregate

"deploy_project_internal":

System.ComponentModel.Win32Exception: A required privilege is not held by the client

System.ComponentModel.Win32Excepbon:

at Microsoft. SqlServer. IntegrationServices.Server.ISServerProcess.StartProcess(Soolean

bSuspendThread)

at Microsoft.SqlServer.IntegrationServices.Server.ServerApi.DeployProjectlnternal(SqlInt64 deployld,

SqIInt64 versionld, SqlInt64 projectld, SqlString projectName)

. (Microsoft SQL Server, Error: 6522)

View 0 Replies View Related

Login Failed- User: Reason: Not Defined As A Valid User Of A Trusted SQL Server Connection.

Apr 5, 1999

Hi,

i'm a newbie in SQL. I can't connect to SQL 6.5 server using ASP ADO. Below is the program code

<%
set conn=server.createobject("adodb.connection")
conn.open "dsn=Central;uid=sa;pwd="
%>

An error message appears
Login failed- User: Reason: Not defined as a valid user of a trusted SQL Server connection.

I've already created a ODBC System DSN named Central
parameter Server Local and used Trusted connection checked.

Then under sql Manager there is a sa with a blank password.

Please help.

View 1 Replies View Related

Linked Server Doesn't Return All Rows For Some Tables

Jun 4, 2007

Hello,



I created a linked server in sql server 2005 which links to a AS400 DB. I use ODBC driver.

For some tables, it return all data but for another tables, it only return part of the rows.

How it may happen?



Thanks

View 1 Replies View Related

User Defined Functions In SQL Server 7.0

Nov 4, 2004

hai,

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

Thnks in advance

View 3 Replies View Related

User Defined Functions In SQL Server 7.0

Jul 20, 2005

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,

View 2 Replies View Related

How To Run User Defined Function On Sql Server 2005

Dec 24, 2007

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?
 
Regards
Jerminxxx

View 7 Replies View Related

User Defined Funcyions In Sql Server 2000

Oct 14, 2006

hi,it might be a very stupid question but i want to know whether anexecution plan is created in user defined functions in sql server 2000like stored procedures.Thanks.

View 4 Replies View Related

Calling User Defined Function From Other Server

May 25, 2006

I have UDF in a database on SQL2000 server. Is it possible to call this UDF from other server (SQL2005)? I did setup a linked server to SQL2000

Call to the following function returns an error:

Msg 207, Level 16, State 1, Line 1

Invalid column name 'srv2000'.

select [srv2000].db_test.dbo.F_TEST()

View 4 Replies View Related

C# User Defined Function: Is There A Way To Access The Name Of The Sql Server?

Apr 26, 2006

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.

Thanks in advance,

Sean

View 4 Replies View Related

User-defined Fun Or The System-defined Fun

Apr 2, 2008

hai,

how can i identify the function is user defined or the system defined function....................

View 1 Replies View Related

How To Create User Defined Functions In Sql Server 2005

Mar 17, 2008

 Hi ,      how to create user defined functions in sql server 2005 to retrive data from multiple tables in asp.net....... Thank U! 

View 4 Replies View Related

Is There A User Defined Row Type Ability In MSSQL Server?

Sep 9, 2004

Hi all. We have a mix of informix and mssql server and I want to know if something we do in informix has an analogous feature in MSSQL. We can define a "row type" in informix, like so:

create row type name_1(fname char(20),lname char(20));

The when we create any table that includes a first and last name, we do so using this row type like so:

create table sometable(name name_1, some column,...etc)

This allows us to set a standard for certain common fields and avoids having different developers build the same type of field in more than one way, different lengths, etc.

Is there a similar function in MSSQL server?

View 4 Replies View Related

User Defined Aggregate Function In SQL Server 2000?

Feb 19, 2008

Is it possible to define your own function? If so could you give me an example.

Keep in mind that I said in SQL Server 2000. I want no CLR SQL Server 2005 solutions.

Cheers,
David

View 1 Replies View Related

Retrieve The Version Of SQL Server From Within A User Defined Function

Sep 12, 2006

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?

View 4 Replies View Related

SQL Server 7 Error 15023, User Or Role Exists

Feb 9, 2000

SQL Server 7
i did a restore of a database, then tried to add the User login
to it, but when i select database access, i get the followinf error :-
"Error 15023, user or role already exists !

the user did exist on the Database, but when i select Database,Users or
Database,Roles the User doesn't exist !! so i can't drop it !

any ideas ??

View 2 Replies View Related

Manually Defined Delete Command Doesn't Work

Feb 16, 2008

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

View 2 Replies View Related

SQL Server User Defined Function Treating String As Integer

Oct 3, 2006

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

View 3 Replies View Related

Edit User-defined Data Type In Microsoft SQL Server

Mar 20, 2006

Hi,

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 ?


Thanks in advance ..

-Sudhakar

View 5 Replies View Related

SQL Server 2014 :: User Defined Function - Using Table Name As Variable

Aug 9, 2014

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)

[Code] ....

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.

View 9 Replies View Related

Login Failed: Reason: User &#39;_&#39; Not Defined As A Valid User Of Trusted

Jan 26, 2000

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.

View 1 Replies View Related

[OT] User-Defined String Functions MS SQL Server 2005 Transact-SQL SQLCLR .Net

Feb 7, 2006

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

With the best regards.

View 1 Replies View Related

SQL Server 2008 :: Create Indexed Views On User Defined Functions?

Jul 14, 2015

I am creating mateialized view but it is failing with error that it can't be schema bound.

The query I am working to create materialized view are having joins with different tables and function.

Is it possible to create Indexed views on user defined functions?

View 2 Replies View Related

SQL 2012 :: Not Exists Linked Server Object Query

Aug 10, 2015

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,

[Code] ......

View 4 Replies View Related

Stored Procedure That Checks To See Whether A Value Exists And Only Enters Data If It Doesn't

Feb 4, 2008

Hi All,

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 -

@siteid
@email

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 -,

@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)

together with a message that says "This Username already exists"

Does anyone have any idea how to do this???

Many thanks

View 6 Replies View Related

Using User Defined Data Type (UDT) In A Sql Server 2005 Database + Window Application.

Feb 18, 2006

Hi,

I am using VS2005 C# + sql server 2005 Express edition.

I need to using a database that uses my own defined data types to define its tables columns.

I already have designed a Database projact and create the new UDT as follows:



Create a new Database project in the Visual C# language nodes.


Add a reference to the SQL Server 2005 database that will contain the UDT.


Add a User-Defined Type class.


Write code to implement the UDT.


Select Deploy from the Build menu.

Now I need to ask some quistions:

1- When I try to add a new query to a table that contains my new data type in its columns,if I try to exexute the query the next message appears:

'Execution of user code in the .Net framework is disabled. Enable "clr enabled" configuration option'.

How can I doing that??

2- I need to use that database - which has the new data type - in a traditional ' Visual C# Windows Application' instead of 'Database', but:

when I try to add a new Data Source that contains the tables that have the new data types in its definitions, the next message appears:

'<AyaDatabase.dbo.MyNewUDTTable>

User-defined types(UDTs)are not supported in the Dataset Designer.'

So, how can I resolve that problem??

please help me.

Thanks in advance for any help.

Aya.



View 4 Replies View Related

Return Value For NOT EXISTS

Oct 24, 2006

Hi

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.

Regards

Neil

View 6 Replies View Related







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