.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Scalar Function Variable length return value

Posted By:      Posted Date: September 02, 2010    Points: 0   Category :Sql Server
Would like to write a Scalar Function that returns a variable length string of spaces. Length being determined by an input parameter (int) Maybe I am missing the obvious but it seems impossible because you have to declare the size  of the varchar return variable I know there is a performance hit using scalar functions but we are generating a view that emulates an old fixed file format with lots of unused fields. So it will make the resultant view readable. So I am looking to do select goodfield1,fnSpace(5) as Unusedfield1,goodfield2,fnSpace(10)... from MyView thanks Bob

View Complete Post

More Related Resource Links

Function to return number


I am trying to create a function that  return the number part within / / from a url if exist:

For exmple:

http://www.mydomain.com/Mobile_Exec/1234/Orange_revamps_business_website.aspx SHOULD RETURN "\1234\"

http://www.mydomain.com/News/Orange_revamps_business_website.aspx    SHOULD RETURN "\\"

http://www.mydomain.com/News/Features/627362/Orange_revamps_business_website.aspx SHOULD RETURN "\627362\"

I prefer if possible to use regular expression. Any help would be really appreciated.

Function and Variable declaration with out an 'As' clause


I get a message - "Function without an 'As' clause" for HMAC_MD5 and "Variable declaration without an 'As' clause" for Key and Value.  But the code works.  But the messages still exists.  Is there a way to fix this to avoid the messages?

Function HMAC_MD5(ByVal Key, ByVal Value)


Must Declare a Scalar Variable @ActName

It is a stored procedure and I am trying to qualify O.FullName using a parameter but I am getting error message "Must Declare a Scalar Vaiable @ActName" Not sure why No idea what is wrong on this. The parameter is correct as @ActName SELECT A.AccountID, A.AccountGID, AT.Name AS Type, M.MarketSegmentID, L.LeadSourceGID, O.EmployeeID, O.FullName AS Owner, CASE WHEN Adr.City IS NULL OR Adr.City = ' ' THEN 'BLANK CITY' ELSE Adr.City END AS City, CASE WHEN Adr.Country IS NULL OR Adr.Country = ' ' THEN 'BLANK COUNTRY' ELSE Adr.Country END AS Country, CASE WHEN Adr.StateProvince IS NULL OR Adr.StateProvince = ' ' THEN 'BLANK STATE' ELSE Adr.StateProvince END AS State, CASE WHEN Adr.AddressLine1 IS NULL OR Adr.AddressLine1 = ' ' THEN 'BLANK ADDRESS' ELSE Adr.AddressLine1 END AS Address1, CASE WHEN Adr.Postal IS NULL OR Adr.Postal = ' ' THEN 'BLANK POSTAL' ELSE Adr.Postal END AS Postcode, CASE WHEN A.Name IS NULL OR A.Name = ' ' THEN 'BLANK ACCOUNT NAME' ELSE A.Name END AS ActName, CASE WHEN A.PhoneNumber IS NULL OR A.PhoneNumber = ' ' THEN 'BLANK PHONE #' ELSE A.PhoneNumber END AS Phone, CASE WHEN SIC.Name IS NULL OR SIC.Name = ' ' THEN 'BLANK SIC CODE' ELSE SIC.Name END AS SIC, CASE WHEN M.Name IS NULL OR M.Name = ' ' THEN 'BLANK MKT SEG' ELSE M.Name END AS MktSeg, CASE WHEN A

Improper execution plan with scalar function

Run the following sql in an empty db(in my case, sql 2008): create table t (id int) go declare @i int set @i = 1 while @i <= 1000 begin insert t values (@i) set @i = @i + 1 end go create function f(@i int) returns int as begin return (select count(*) - 10000+@i from t) end go Then run the following sql a, it's slow, used 6 seconds, yet sql b is very fast. Their execution plans are totally different. Can sql a run as fast as sql b? If not, why? -- sql a declare @i int set @i = 500 select * from t where id = (select dbo.f(@i)) -- sql b select * from t where id = (select dbo.f(500)) Thanks!

SSRS 2005 SP3 - Instr function with Global Variable fails in Subscriptions

Hi I have a very simple expression in a text box in my report:  INSTR(Globals!ReportServerUrl,"Report").  This expression should return the integer value of where the string "Report" appears in the Global variable for the ReportServerUrl.  Simple enough.  This works fine for generating the report directly through report manager, and in my case returns a value of 20. However when run as a subscription and emailed to myself the value returned is 0. This suggests that the search string does not appear in the Global variable. In the reportserver config file the URLroot tag contains the correct server address - http://myserver/reportserver. Does anyone know why i should be experiencing this issue with my subscriptions?  Your help would be much appreciated! Thanks Guy  Guy

Pipe SQL Query function into Variable with no column headings

Hello, I don't know if I'm in the right forum but could not find one for Powershell. I need to return 1 piece of data from a sql database to a variable. I don't want the column headings. Here's what I have so far:         Function GetMenuGroup ([int]$StoreID) { $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server=Servername;Database=DatabaseName;Integrated Security=True" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = "exec storedProcedure $StoreID" $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet)|out-null $SqlConnection.Close() $DataSet.Tables[0] } $MG=Getdata 2 |out-string Here's the results I get now - How do I return just "DatathatIreallyWant" in a string variable? I don't want the column or underline. ColumnName ------------- DataThatIreallyWant   Thank you, MG2

Java function needs to return only two places

I have this java function that works great, but I need it to only return two decimal places like 333.33. Currently it returns 333.3333333333. What can I do to modify this? plannedpmt = (pledgedamt / numberpayments) 

Must declare the scalar variable "@NieuwsID".

Hallo, I get the error message:  Must declare the scalar variable "@NieuwsID". Can anybody help? <%@ Page Title="" Language="VB" MasterPageFile="~/Nieuws/MasterPageNieuws.master" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="Nieuws_Default" %> <asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server"> <link href="../Inloggen.js" type="text/javascript" /> <script type="text/javascript"> function toonPopUp() { var popUpPanel = document.getElementById('popUpPanel'); popUpPanel.style.display = 'block'; } function verbergenPopUp() { var popUpPanel = document.getElementById('popUpPanel'); popUpPanel.style.display = 'none'; } </script> <style type="text/css"> #popUpPanel { position: absolute; width: 200px; height: 200px; top: 80px; left: 400px; display: none; border: medium #2B8ED6 solid; padding: 5px; background-color: #FFFFFF; color: #333333; vertical-align: middle; } #xlogon { border-style:none; font-family: Verdana; font-size:medium; color: #2B8ED6; background-color: Transparent; } #logtekst { text-align:left; top: 20px; width: 190px; } </style> </asp:Content> <asp:Content ID="Content2" ContentPlaceHolderID="C

Return linq query in function

I try to return a var query from a function, but i'll get the error: Error:Error 41 Cannot implicitly convert type 'System.Collections.Generic.IEnumerable<AnonymousType#1>' to 'System.Collections.Generic.IEnumerable<System.Xml.XmlElement>'. An explicit conversion exists (are you missing a cast?)  Code: protected static IEnumerable<XmlElement> GetModels(byte MerkID) { string rawXML = AutoDisk.GetModels(12345, true, MerkID, "2010", ""); XmlDocument xml = new XmlDocument(); xml.LoadXml(rawXML); var query = from feed in xml.DocumentElement.ChildNodes.Cast<XmlNode>() select new { nModelID = feed.SelectSingleNode("nModelID").InnerText, Modelnaam = feed.SelectSingleNode("Modelnaam").InnerText }; return query; } Whats wrong and how can i make this working ?  

Error: "Select statements included within a function cannot return data to a client"

hi all, Not sure if there is an easier way to do this but this is my first ever Function I created so far. I am getting the error: "Select statements included within a function cannot return data to a client" All I am trying to do is ...if the date is NOT passed then apply NULL as the default value and run the SELECT statement. If the date IS NOT null then run a different SELECT statement. CREATE FUNCTION [SchemaTest].[fn_Test] ( @instance as uniqueidentifier ,@date as datetime = null ) RETURNS varchar(8000) AS BEGIN If (@date is null) --@date = null ?? Begin Select * From Uds.RealtimeLogging Where instanceID = @instance End Else Begin Select * From Uds.RealtimeLogging Where instanceID = @instance and [timeStamp] > @date End Return @stepIds END Thank you!JCD

SQL Reporting Services 2005: Must declare scalar variable

I have a report that calls a stored procedure that builds a temp table and then runs a select that joins on that temp table. The only parameter I have is @Name, an optional parameter that can be filled or NULL.   I keep getting the error "An error has occurred during local report processing. An error has occurred during report processing. Query execution failed for data set. Must declare the scalar variable "@Name".   I have checked to make sure this parameter is connected to the dataset by looking at the parameter tab. I've searched on posts but haven't seen an answer. Does anyone have any idea what this could be about and how to fix it?

Gridview control Delete error -- Must declare the scalar variable "@GradeID"

 Hi All,Pardon me if this seems redundant. I have searched the archives and found many references to this type of error. Most (all?) boil down to a mismatch between the token for the databound field and the sql parameter. I have stared at this code for a long time and can see nothing wrong with it. In fact, it is so simple that I started over from scratch using only the designer (ie I wrote no code), so I don't see how it possible to have a name mismatch. I would very much appreciate any help figuring out what is the problem here. Thank you very much in advance.-Roger   Server Error in '/ssp/inet220a06' Application. Must declare the scalar variable "@GradeID". Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Must declare the scalar variable "@GradeID". Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

MVC Validation Pattern Question, Especially for Variable Length Lists and Conditional Validation


I've spent a lot of time exploring the various patterns of MVC validation. In particular, I am interesting in learning best-practice for validating a view which posts an enumerable collection back to the controller. One approach that is appealing involves posting the form via Ajax to check validation (http://jvance.com/blog/2010/02/20/MakingAnAjaxFormWithJQueryInASPdotNETMVC.xhtml). Obviously, the negative is that this is not true client-side validation since there is a "hidden" server round-trip, but the effect is comparable, and obviously client-side validation alone will never stand--server-side always needs to be done as well.

The CodePlex repository for this demo: http://mvcajaxvalidation.codeplex.com/SourceControl/list/changesets

The main reason I started heading this route is because in the course of writing lots of custom data annotation model validators (think "RequiredIf" or "RequiredWhenContains") it became very evident that the wiring to the client-side gets complicated. I was mostly able to pull it off using the custom validators' GetClientValidationRules() plus jQuery validator extensions (.addMethod('requiredif'...), ho

function or query to return continuous span of months $ years as rows in query



I am using SS2008.

There are many situations in which I need to return a table where the rows are EVERY calendar month between two dates, not just the months where there is data present.

Is there a function that would take a beginning and end date mmddyyyy which would do this?

For example if I wanted 1/2004 through 8/2010?




and so on to





Thanks in advance,




Simple return value from WebMethod from Ajax function using JQuery


I have an Ajax function called from JQuery that goes to a webservice to return a value.  I need a SIMPLE example on how I can do this.  I've been going nuts with serializing and every other aspect of this topic. 

I need to return either an ArrayList with ONE string field or a DataTable of some kind.  Either way, I'm populating it into a DropDownList.

I'm willing to consider alternatives to this idea. (Background info - I get a value from a textbox and I need to run it through a DB to get an associated value or set of values).  I'm being really general so that someone can show a simple example.  Thanks.

Scalar Variable Exception


I am using an SQL datasource to update a table based on data passed by FCKEditor. My code is found below. The data loads in fckeditor fine, but when the save button is clicked, i get a Scalar Variable Exception, such is the following:

Incorrect syntax near 'nvarchar'.
Must declare the scalar variable "@". 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'nvarchar'.

return table from function (function has WITH common_table_expression )


I am trying to create following function which should return table but when I try to create it I get error message as "Select statements included within a function cannot return data to a client.". I am looking for workaround for this issue.



function dbo.GetCategoryIdTillBase(@Category_Id as char

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend