Using Scalar Functions In Views

Sep 19, 2007

I have a view that contains a complex query. A few of the columns
call a function that returns a specific output. I also use a function
to do a join as well.

For example:

SELECT l.ID, dbo.getStatus(l.ID) AS statusID
FROM tableName A
LEFT OUTER JOIN dbo.Status_LKP s ON dbo.getStatus(l.Leg_ID) =
s.statusID

For 800 records, this query takes about 9 seconds. I realize that for
each record, the function is executed on a per row basis, so I am
looking for alternatives.

Does anyone know of other ways to accomplish something of the same?
Basically I would like to include UDFs in a query and use those UDFs
in the where and join clauses.

Thanks

View 2 Replies


ADVERTISEMENT

Why Are Scalar Functions Soooo Sloooow?

Jul 20, 2005

Hi all,I've been using scalar functions as a way to perform some complex datatransformation operations, and I've noticed that scalar functionsreaaaaalllllyyyy sloooowwwwww thiiiiiings dooooooown. I expect slow-down, ofcourse, and would even not be surprised at slow-downs up to a factor of, say50:1, but I'm seeing slow-downs more like 1000:1 or 100000:1. I'm sure itwould actually be faster to actually export a table, use VB to process it,then import it back in.

View 7 Replies View Related

How To Avoid Using Scalar User Defined Functions

Sep 26, 2015

Following the horrendous performance of Scalar UDFs demonstrated in this thread URL....(Scalar UDF 10x slower than writing the code, long hand, in the SELECT) I now have the worry that their widespread usage in my code needs to be refactored. I guess pretty much any single-line non-table-valued scalar-function would do

CREATE FUNCTION dbo.FNSafeString
(
@strValuevarchar(8000),-- STRING Value to convert to SAFE string
@intModeint = 0-- 0=NULL as '[NULL]', 1=NULL as ''
)
RETURNS varchar(8000)
WITH SCHEMABINDING
AS
BEGIN
RETURN COALESCE(@strValue,
CASE WHEN @intMode = 0
THEN '[NULL]'
ELSE ''
END
)
END

Its a little bit too big to really want to have to write it in each place where I need to use it, and its just big enough that it is worth centralising, and it might just be that I could code it more efficiently / different / handle more "edge cases" in the future.I do have even more straightforward UDFs - just to avoid typing something "Lengthy"

CREATE FUNCTION dbo.FNDateMidnight
(
@dtDateTimedatetime-- Date/Time Value to adjust
)
RETURNS datetime
WITH SCHEMABINDING
AS
BEGIN
RETURNDATEADD(Day, DATEDIFF(Day, 0, @dtDateTime)+1, 0)
END

Perhaps I should consider changing them to inline table valued functions, or CROSS APPLY them? but I can't think of a way to centralised them and re-use the code WITHOUT a function, can I?

View 9 Replies View Related

Can't Use The NTEXT Datatype In SQLCLR Scalar-valued Functions

Mar 19, 2007

From the SQL Server documentation : "The input parameters and the type returned from a SVF can be any of the scalar
data types supported by SQL Server, except rowversion, text,
ntext, image, timestamp, table, or cursor"This is a problem for me.  Here's what I'm trying to do :I have an NTEXT field in one of my tables.  I want to run regular expressions on this field, and return the results from a stored procedure.  Since SQL Server doesn't provide facilities to perform regular expressions, I need to use an SQLCLR function.  I would have no problem doing this if my field was nvarchar.  However, this field needs to be variable in length - I cannot set an upper bound.  This is why I'm using NTEXT and not nvarchar in the first place.Is there a solution to this problem?  I can't imagine that I'm the only person who wants to pass strings of arbitrary size to an SQLCLR function. 

View 2 Replies View Related

Scalar Variables And Views

Apr 24, 2007

I have a stored procedure that I'm trying too, and must figure out how to convert it to a view. The stored procedure uses two scalar variable for a date range in the WHERE CLAUSE (@StartDate and @EndDate). What can we do in a view if we cannot hard code date ranges? If I use the date field (Paydate) in the view then I cannot get a sum of the Payamt.
I appreciate your help.

View 15 Replies View Related

Return Statements In Scalar Valued Functions Must Include An Argument

Jan 20, 2006

I'm trying to create a SQL server 2000 function that returns a scalar value, but I keep getting the error "Return statements in scalar valued functions must include an argument". Online clarification of this error message is no help at all.I've tried all sorts of combinations of the following, without much luck. Can someone point out my dim-witted mistake, please?ALTER FUNCTION dbo.intCoursesPublic (@intCatID as int)  RETURNS  intASBEGIN RETURN     SELECT COUNT(intCourseID) AS Expr1        FROM    dbo.tbl_guru_course_list            WHERE     (intCatID = @intCatID)END

View 4 Replies View Related

RETURN Statements In Scalar Valued Functions Must Include An Argument ERROR

Aug 16, 2006

Hi,

I am trying to write a function which takes a string as input and returns the computed value.

I need to use the output of this function as a coulmn in another select query.

Here is the code (Example: @Equation = '(100*4)+12/272')

create function dbo.calc(@Equation nvarchar(100))
returns float
as
begin

return exec('SELECT CAST('+@Equation+' AS float)')
end

I am getting this error when i compile it

"RETURN statements in scalar valued functions must include an argument"

Any suggestions would be appreciated.

Please respond

Thanks

View 6 Replies View Related

Views Vs Functions

Aug 22, 2007

I wanna know which adventages and disadventages have the command create funtion over create view or if you know a link that explain that

View 7 Replies View Related

Decrypting Encrypted Views/Sp/Functions.....?

Sep 19, 2006

Hi all,As all of you are aware you can Encrypt your Triggers/Stored Procedures/Views And Functionsin Sql Server with "WITH ENCRYPTION" clause.recently i came across a Stored procedure on the Net that could reverse and decrypt all Encrypted objects.i personally tested it and it really works.That's fine (of course for some body)Now i want to know is it a Known Bug for Sql Server 2000 and is there a permanent solution for Encrypting mentioned objects.Thanks in advance.Best Regards.

View 2 Replies View Related

Views Or Table Valued Functions?

Aug 11, 2004

Hi I am writting Stored Procedures that have to be built on the base of other tables specially created for this purpose, is it better to create these intermediate tables as views or as functions returning tables? I guess views would be lighter on performance as they would not be created on the fly?

View 2 Replies View Related

Table Valued Functions Vs Views

Apr 24, 2008

Are there any disadvantages in respect to performance in using table valued functions instead of using a view.

Thanks...

View 3 Replies View Related

Views / Stored Procedures / Functions

Dec 1, 2005

Hi All,

Novice question. Would someone explain tell me what a view is used for? Also I am confused about the difference between a function and a stored procedure. They both seem like functions to me.

View 7 Replies View Related

Enabling Views, Functions Toggle

Oct 23, 2007

Hello:

Is it possible to standardize sql views or functions to filter data for the user, before they view the report? For example if I have a SQL view that filters for "green, cotton-made socks", can this somehow be an option among others before a user presses "view report?"

A response is greatly appreciated. If this is possible, how ?


Many thanks.

View 1 Replies View Related

Difference Among Store Procedure, Functions And Views

Feb 15, 2006

Hi,

What is the basic difference between

store procedure, Functions and views

Thanks

View 2 Replies View Related

Design Question: Nested Views And Functions?

Jul 23, 2005

I am working in a project where the business model is complex enoughthat many common retrieval functions become difficult to develop andmaintain in single query statements or functions.I have found the logic is easier to implement (and later modify whenthe code is no longer freshly remembered), by implementing theprocessing layers in nested views, and functions that callsub-functions (UDFs), not too unlike object based programming ornon-DBMS procedural languages. In some cases, the views and functionsare nested three deep.So far, from a design standpoint, this practice is working very well.Code that would be a recusive mess is reduced to two or three simplercode blocks. With the proper indexing and query structure, performanceseems to be satisfactory (time will tell).In MS SQL Server, is there anything which makes this practice unsound?Examples:CREATE VIEW vw2 AS SELECT * FROM tbl1 WHERE ...CREATE VIEW vw3 AS SELECT * FROM vw2 WHERE ...Application uses: SELECT * FROM vw3-or-CREATE FUNCTION udf2 AS SELECT * FROM tbl1 WHERE ...CREATE FUNCTION udf3 AS SELECT * FROM udf2 WHERE ...Application uses: SELECT udf3(param) AS value

View 5 Replies View Related

Enabling Views, Functions Through Report Builder?

Oct 23, 2007

I was wondering if it was possible to incorporate sql views or functions that filter data on the top bar for users when they run a report.

a response is greatly appreciated! If so, how?

View 1 Replies View Related

Starting Out With : Views, Stored Procedures, Functions, Synonyms

Mar 22, 2006

Hi,Right, i have a fairly good understanding of SQL. However, i have a fairly basic understanding of SQL Server.I know Storedprocedures are really good and i'm starting to use them. I understand they are good for making inserting, updating very easy.However when you look at a SQL Server database you get various folder, this leaves me a little confused with what they are all used for? whats the difference between these?Thanks in advance!sorry for the basic question, i'll try to challange you next time

View 1 Replies View Related

The Query And The Views Or Functions In It Exceed The Limit Of 256 Tables.

Feb 22, 2007

Yes, I do know what this means and why the error is thrown but this is not my question.

I have two servers that are both running Windows Server 2003 and SQL Server 200 SP3. Below are the results from both servers using @@version

Sever 1 (BB)

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

Server 2 (Genesis)

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

These servers are identical or so it seems. I've got a real ugly query that uses views and a derived table to get results. The problem is the 256 limit message only comes up on one server and on the other (Genesis) the query runs fine. I get the error though it reads a 260 limit on a box with SP4 applied. I've also run the query on a box that is Windows 2003, sql2k and sp4 and the query runs but not on a similar server here. This is all very odd. Please note that the database structure, views, etc are all exactly the same as far as I know.

Any suggestions? There seems to be no pattern between versions of Windows and/or SP levels.

View 1 Replies View Related

Do You Replicate Views, Stored Procedures, And User Functions?

Jul 22, 2007

Greetings,



We have recently begun using transactional replication to keep the data in our SQL Servers synchronized in a geographically dispersed environment. We replicate our tables but we have never replicated views, stored procedures, or user functions in our production systems. We are thinking of doing so but wonder if the overhead of running the replication agents doesn't outweigh the benefits of having replication assist with the occassional change to these design elements.



Is anyone on this forum replicating views, sprocs, and user functions? What has your experience been?



Thanks for any ideas that you share.



BCB

View 4 Replies View Related

A Stored Proc To Search All Procs, Views, Triggers And Functions

Jul 20, 2005

I developed a search stored proc that searches all orsome of Procs, Views, Triggers and functions. Would anyone be interestedto see it posted here?Do you have any suggestions about other places, websites forums ...., toshare something I have developed?Thanks you for your response in advancePLease send me emailJoin Bytes! {Remove ### before responding}

View 1 Replies View Related

Programmatically Extract Stored Procedures, Views, Tables, And Functions

May 21, 2008



Hi Folks,


I am writing a program that transforms a generic MS SQL database to make it compatible with our application. In order to make the transformation, I have to access all the elements of the generic database programmatically.

I know that the Master database contains all this information. Is there a query that allows me to access the "non-system" tables, sps, views, and functions?

For Tables, I need to know the Name(s) of the tables, the column names, the column type, ALLOW Nulls, Primary Key, Identity Seed settings, and Triggers.

For SPs, I need to know the Name(s) and access the SP source code (assuming it's unencrypted).

For Views, I need to know the Name(s) and access the Views Source

For functions, I need to know the Name(s) and access the function source.


I can see various tables in the Master database from management studio, like sys.objects, sys.tables - which look like they have the info I need. However, when I run a query against master like:

select * from sys.objects .. I get an error:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.



Thank you in advance.



View 13 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

ORDER BY Clause Is Invalid In Views / Inline Functions / Derived Tables / Subqueries

Sep 25, 2013

The data I am pulling is correct I just cant figure out how to order by the last 8 numbers that is my NUMBER column. I tried adding FOR XML AUTO to my last line in my query: From AP_DETAIL_REG where AP_BATCH_ID = 1212 and NUMBER is not null order by NUMBER FOR XML AUTO) as Temp(DATA) where DATA is not null

but no change same error.
Output:
1234567890000043321092513 00050020

Select DATA from(
select '12345678'+
left( '0', 10-len(cast ( CONVERT(int,( INV_AMT *100)) as varchar))) +
cast (CONVERT(int,(INV_AMT*100)) as varchar) +
left('0',2-len(CAST (MONTH(DATE) as varchar(2))))+
CAST (MONTH(DATE) as varchar(2)) +
left('0',2-len(CAST (day(CHECK_DATE) as varchar(2)))) +
CAST (day(DATE) as varchar(2))+right(cast
(year(DATE)

[code]....

View 6 Replies View Related

Are Embedded Views (Views Within Views...) Evil And If So Why?

Apr 3, 2006

Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.

View 15 Replies View Related

SQL Server 2005: CLR Functions Vs SQL Functions

May 26, 2006

I was playing around with the new SQL 2005 CLR functionality andremembered this discussion that I had with Erland Sommarskog concerningperformance of scalar UDFs some time ago (See "Calling sp_oa* infunction" in this newsgroup). In that discussion, Erland made thefollowing comment about UDFs in SQL 2005:[color=blue][color=green]>>The good news is that in SQL 2005, Microsoft has addressed several of[/color][/color]these issues, and the cost of a UDF is not as severe there. In fact fora complex expression, a UDF in written a CLR language may be fasterthanthe corresponding expression using built-in T-SQL functions.<<I thought the I would put this to the test using some of the same SQLas before, but adding a simple scalar CLR UDF into the mix. The testinvolved querying a simple table with about 300,000 rows. Thescenarios are as follows:(A) Use a simple CASE function to calculate a column(B) Use a simple CASE function to calculate a column and as a criterionin the WHERE clause(C) Use a scalar UDF to calculate a column(D) Use a scalar UDF to calculate a column and as a criterion in theWHERE clause(E) Use a scalar CLR UDF to calculate a column(F) Use a scalar CLR UDF to calculate a column and as a criterion inthe WHERE clauseA sample of the results is as follows (time in milliseconds):(295310 row(s) affected)A: 1563(150003 row(s) affected)B: 906(295310 row(s) affected)C: 2703(150003 row(s) affected)D: 2533(295310 row(s) affected)E: 2060(150003 row(s) affected)F: 2190The scalar CLR UDF function was significantly faster than the classicscalar UDF, even for this very simple function. Perhaps a more complexfunction would have shown even a greater difference. Based on this, Imust conclude that Erland was right. Of course, it's still faster tostick with basic built-in functions like CASE.In another test, I decided to run some queries to compare built-inaggregates vs. a couple of simple CLR aggregates as follows:(G) Calculate averages by group using the built-in AVG aggregate(H) Calculate averages by group using a CLR aggregate that similatesthe built-in AVG aggregate(I) Calculate a "trimmed" average by group (average excluding highestand lowest values) using built-in aggregates(J) Calculate a "trimmed" average by group using a CLR aggregatespecially designed for this purposeA sample of the results is as follows (time in milliseconds):(59 row(s) affected)G: 313(59 row(s) affected)H: 890(59 row(s) affected)I: 216(59 row(s) affected)J: 846It seems that the CLR aggregates came with a significant performancepenalty over the built-in aggregates. Perhaps they would pay off if Iwere attempting a very complex type of aggregation. However, at thispoint I'm going to shy away from using these unless I can't find a wayto do the calculation with standard SQL.In a way, I'm happy that basic SQL still seems to be the fastest way toget things done. With the addition of the new CLR functionality, Isuspect that MS may be giving us developers enough rope to comfortablyhang ourselves if we're not careful.Bill E.Hollywood, FL------------------------------------------------------------------------- table TestAssignment, about 300,000 rowsCREATE TABLE [dbo].[TestAssignment]([TestAssignmentID] [int] NOT NULL,[ProductID] [int] NULL,[PercentPassed] [int] NULL,CONSTRAINT [PK_TestAssignment] PRIMARY KEY CLUSTERED([TestAssignmentID] ASC)--Scalar UDF in SQLCREATE FUNCTION [dbo].[fnIsEven](@intValue int)RETURNS bitASBEGINDeclare @bitReturnValue bitIf @intValue % 2 = 0Set @bitReturnValue=1ElseSet @bitReturnValue=0RETURN @bitReturnValueEND--Scalar CLR UDF/*using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;public partial class UserDefinedFunctions{[Microsoft.SqlServer.Server.SqlFunction(IsDetermini stic=true,IsPrecise=true)]public static SqlBoolean IsEven(SqlInt32 value){if(value % 2 == 0){return true;}else{return false;}}};*/--Test #1--Scenario A - Query with calculated column--SELECT TestAssignmentID,CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END ASCalcColumnFROM TestAssignment--Scenario B - Query with calculated column as criterion--SELECT TestAssignmentID,CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END ASCalcColumnFROM TestAssignmentWHERE CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END=1--Scenario C - Query using scalar UDF--SELECT TestAssignmentID,dbo.fnIsEven(TestAssignmentID) AS CalcColumnFROM TestAssignment--Scenario D - Query using scalar UDF as crierion--SELECT TestAssignmentID,dbo.fnIsEven(TestAssignmentID) AS CalcColumnFROM TestAssignmentWHERE dbo.fnIsEven(TestAssignmentID)=1--Scenario E - Query using CLR scalar UDF--SELECT TestAssignmentID,dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumnFROM TestAssignment--Scenario F - Query using CLR scalar UDF as crierion--SELECT TestAssignmentID,dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumnFROM TestAssignmentWHERE dbo.fnIsEven(TestAssignmentID)=1--CLR Aggregate functions/*using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]public struct Avg{public void Init(){this.numValues = 0;this.totalValue = 0;}public void Accumulate(SqlDouble Value){if (!Value.IsNull){this.numValues++;this.totalValue += Value;}}public void Merge(Avg Group){if (Group.numValues > 0){this.numValues += Group.numValues;this.totalValue += Group.totalValue;}}public SqlDouble Terminate(){if (numValues == 0){return SqlDouble.Null;}else{return (this.totalValue / this.numValues);}}// private accumulatorsprivate int numValues;private SqlDouble totalValue;}[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]public struct TrimmedAvg{public void Init(){this.numValues = 0;this.totalValue = 0;this.minValue = SqlDouble.MaxValue;this.maxValue = SqlDouble.MinValue;}public void Accumulate(SqlDouble Value){if (!Value.IsNull){this.numValues++;this.totalValue += Value;if (Value < this.minValue)this.minValue = Value;if (Value > this.maxValue)this.maxValue = Value;}}public void Merge(TrimmedAvg Group){if (Group.numValues > 0){this.numValues += Group.numValues;this.totalValue += Group.totalValue;if (Group.minValue < this.minValue)this.minValue = Group.minValue;if (Group.maxValue > this.maxValue)this.maxValue = Group.maxValue;}}public SqlDouble Terminate(){if (this.numValues < 3)return SqlDouble.Null;else{this.numValues -= 2;this.totalValue -= this.minValue;this.totalValue -= this.maxValue;return (this.totalValue / this.numValues);}}// private accumulatorsprivate int numValues;private SqlDouble totalValue;private SqlDouble minValue;private SqlDouble maxValue;}*/--Test #2--Scenario G - Average Query using built-in aggregate--SELECT ProductID, Avg(Cast(PercentPassed AS float))FROM TestAssignmentGROUP BY ProductIDORDER BY ProductID--Scenario H - Average Query using CLR aggregate--SELECT ProductID, dbo.Avg_CLR(Cast(PercentPassed AS float)) AS AverageFROM TestAssignmentGROUP BY ProductIDORDER BY ProductID--Scenario I - Trimmed Average Query using built in aggregates/setoperations--SELECT A.ProductID,CaseWhen B.CountValues<3 Then NullElse Cast(A.Total-B.MaxValue-B.MinValue ASfloat)/Cast(B.CountValues-2 As float)End AS AverageFROM(SELECT ProductID, Sum(PercentPassed) AS TotalFROM TestAssignmentGROUP BY ProductID) ALEFT JOIN(SELECT ProductID,Max(PercentPassed) AS MaxValue,Min(PercentPassed) AS MinValue,Count(*) AS CountValuesFROM TestAssignmentWHERE PercentPassed Is Not NullGROUP BY ProductID) BON A.ProductID=B.ProductIDORDER BY A.ProductID--Scenario J - Trimmed Average Query using CLR aggregate--SELECT ProductID, dbo.TrimmedAvg_CLR(Cast(PercentPassed AS real)) ASAverageFROM TestAssignmentGROUP BY ProductIDORDER BY ProductID

View 9 Replies View Related

Large Views Vs Multiple Small Views

Sep 6, 2007

Which is more efficient? One large view that joins >=10 tables, or a few smaller views that join only the tables needed for individual pages?

View 1 Replies View Related

Recompiling Views That Reference Other Views

Jun 28, 2007

Hello.

Newbie here. I've only been using SQL for about a year now and have some minor questions about sql objects that reference other objects.



We have some views which reference other views in the joins. I will call one the primary view and the one being referenced in the joins as the secondary view.

Recently we made changes to the secondary view.

After which the primary views which referenced it would not work because of this change and had to be 'refreshed' by using drop/create scripts which essentially just dropped it and recreated the exact same view. I do not recall the exact error message that was returned other than it seemed to suggest that it could no longer see the secondary view since it had been changed. Nothing in the primary view was changed in any way, just the secondary.



Some here where I work have suggested off hand that this was a recompile of the primary view because the contents of the secondary changed.

My questions are:

1. Exactly why did this happen and is there a proper name for it when it does?

2. The same problem does not seem to occur when we have stored procedures referencing views in the joins which had just been changed. Why is that?



Thanks for any help on the matter. I greatly appreciate it.

View 3 Replies View Related

Quicky : Views Of Views Of Views

Feb 22, 2007

Hello,

to make a report easier I'm developing it using a view of joined views of joined views.

Is there any significant performance penalty as opposed to just having one big select?

Cheers.

View 1 Replies View Related

Need Help - UDF SQL 00 To Scalar SQL 05

Jul 7, 2006

I'm having trouble with the UDF's I had in SQL 2000 that were migrated to SQL 2005 under the SCALAR FUNCTION item. Before in 2000 you just created the UDF, named it and saved. IN SQL 2005 you create/modify and then have to save it as a .SQL file to a folder. First of all, what folder should it go to so that all can use and secondly, when I do this and run the functions it doesn't work... I need to modify the scalar function and
save it with out saving to a .SQL file. How do I do this or is this the new way to create/modify functions. Any help is much appreciated
thanks

View 6 Replies View Related

Scalar Value

Nov 5, 2006

What is the definition of a scalar value in a relational database?

thx,

Kat

View 3 Replies View Related

Execute Scalar ?

Sep 20, 2007

I'm trying to do something like the code below, but it's saying "specified cast is not valid"
If i change the value returned to an "int", it works fine. My issue is, i'd like to get the value returned with more accuracy than an int as there will be 2 decimal places.protected float getProjectHours(string project)
{string selectCmd = "SELECT SUM(hours) FROM tasks WHERE project=@project";
string strConnection = ConfigurationManager.ConnectionStrings["TimeAccountingConnectionString"].ConnectionString;SqlConnection myConnection = new SqlConnection(strConnection);
SqlCommand myCommand = new SqlCommand(selectCmd, myConnection);myCommand.Parameters.Add(new SqlParameter("@project", SqlDbType.VarChar));myCommand.Parameters["@project"].Value = project;
myConnection.Open();float total = (float)myCommand.ExecuteScalar();
myConnection.Close();
return total;
}

View 6 Replies View Related

Must Declare Scalar @.....

Oct 26, 2007

Hi everyone,
I am getting that infamous message on an INSERT Sql query. I am doing everything right by the looks of it. All variables are either passed in through a custom form, or else declared and initialised  in the body of the script.
I post the relevent code below:
SQLsqlInsertEmail = "INSERT INTO CandidateLogins (SiteID, LoginName, CandidateEmail, DateRegistered) " & _" VALUES (@SiteID, @LoginName, @CandidateEmail, @DateRegistered); SELECT SCOPE_IDENTITY()"Try    sqlSetCandidateEmail.Parameters.Add(New SqlParameter("@SiteID", SqlDbType.Int))    sqlSetCandidateEmail.Parameters("@SiteID").Value = SiteID    sqlSetCandidateEmail.Parameters.Add(New SqlParameter("@LoginName", SqlDbType.VarChar))    sqlSetCandidateEmail.Parameters("@LoginName").Value = userName    sqlSetCandidateEmail.Parameters.Add(New SqlParameter("@CandidateEmail", SqlDbType.VarChar))    sqlSetCandidateEmail.Parameters("@CandidateEmail").Value = email    sqlSetCandidateEmail.Parameters.Add(New SqlParameter("@DateRegistered", SqlDbType.DateTime))    sqlSetCandidateEmail.Parameters("@DateRegistered").Value = DateRegistered    sqlSetCandidateEmail = New SqlCommand(sqlInsertEmail, C4LConnection)
    C4LConnection.Open()    CandidateID = sqlSetCandidateEmail.ExecuteScalar()
Catch Exp As SqlException    lblResults.Visible = True    lblResults.Text = "Unable to Register Jobseeker: " & Exp.MessageFinallyC4LConnection.Close()End Try
All the variables passed into the SQL statement are initialised, with SiteID beign set to '0', rather than Null (none of the fields are Nullable in the database table) and I have checked that the SqlDbType's correspend to the Table Definition 
So far as I can discern, everything is correct and as can be seen, I am not using a stored procedure in this instance, but the script falls over be producing the error message "Must Declare Scalar @SiteID", even though SiteID is declared as Int32 further up in the script.
Any help would be appreciated.

View 8 Replies View Related

UD Scalar Function

Mar 5, 2008

I want to add four fields of a table and place the toatal in a new field.Also I wanna have the average of the fields.

For e.g
I have created a marksheet having four subjects.Now I wanna add the subjects and find the average of the subjects and place them in two different fields in the same table along with the respective names in the table.Pls help.

Thanks in advance.

View 8 Replies View Related







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