.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

Is this possible? declare @myVariable varchar(myLength)

Posted By:      Posted Date: October 12, 2010    Points: 0   Category :Sql Server

Can I pass an integer input argument (say myLength) to a stored proc and use it as follows:

         declare @myVariable varchar(myLength);

In other words, the actual length specified for @myVariable can change depending upon the value of myLength used in the proc call. If it is not possible for a local variable, can it be done for a global variable?




View Complete Post

More Related Resource Links


Defines the attributes of a Transact-SQL server cursor, such as its scrolling behavior and the query used to build the result set on which the cursor operates. DECLARE CURSOR accepts both a syntax based on the ISO standard and a syntax using a set of Transact-SQL extensions.

Error converting data type varchar to numeric.


I create a form to record data using ASP.NET and SQL database...
when i run the project show error "Error converting data type varchar to numeric"

what's the problem actually.i try to change in datatype in database..
but still showing error...


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

Varchar string to int - how to convert empty cells to nulls

Hi, I have a large table from which I want to convert certain columns from varchar to int while loading the table to another table. However, I run into problems when the input column contains empty cells. The SSIS data conversion data flow item can't handle these so I tried the script component. With the script component I can convert these empty cells to zeros but after that I'd need to use SQL update to update the zeros to nulls which in the case of a large table with over 3 million rows takes unnecessarily long. So, is there another way?

Must Declare Variable @tableName Error ?

create Procedure dummy ( @tablename nvarchar(20) ) AS Begin select ID,employeename from @tablename End I dont know why SqlServer is not able to search a table name so thought of getting some help from sql Gurus . Any help would highly be appreciated . Thanks .Rajkumar Yelugu

The data types varchar and date are incompatible in the add operator

On the following query I keep getting the error 'The data types varchar and date are incompatible in the add operator'.  As you can see I have tried to convert the date but doesn't seem to work.  The variable @Date1 will start off as a date.  Any suggestions? ' declare @Date1 date declare @SQL1 varchar(2000) set @Date1 = convert(varchar, cast(getdate() as date), 102) set @SQL1 = 'select * from tbl_1 where convert(varchar,cast(tbl_1.Current_dt as date),102) = '+@Date1+'' print @SQL1    LISA86

Replace string in xml without converting to varchar using SQL Server

We have a xml datatype in SQL server which gets a xml file loaded into it. We want to replace some of the content in the xml datatype, but are running into problems converting from xml to varchar(for manipulation purposes), back to xml to output. Does anyone know of a way to replace xml without converting to varchar?Thanks in advance

How to declare a global connection object?

Following the post on declaring a global variable 'theDate' with a defined date format to be used across the web pages, I now encounter another tricky question: Is there a way to declare a global connection object so that I can avoid repeating the same statements (SqlConnection myconn = new SqlConnection(Class1.conn); myconn.Open();) in all the coding page that require a database connection? Thanks.

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

Warning WithEvents variable, how do i declare shadow?

I get this warning , how do i overcome it. I have a grid veiw on a form with one of my rows  called Location.                                                        thanks rob Warning 1 WithEvents variable 'Location' conflicts with property 'Location' in the base class 'Form' and should be declared 'Shadows'. rh


Hi Folks, I downloaded SQL 2008 and installed recently.Everything perfectly works except one thing that I can't use VARCHAR AND CHAR data types anymore.I was using SQL 2005 and I had the same problem there as well. Please helpe to solve this problem

How to declare variable and assign value in xslt

Hi, I am writing xslt to create HTML by using input xml data. The input XML looks like this ID Name  Description 1 Name1 Description Something1 Name1 Description another text2 Name2 Some text2 Name2 Some text22 Name2 Some text2 Here Id is primary, where Name is unique as per ID. I want to show the output the following way Name1   * Description Something   * Description another textName2   * Some text   * Some text2   * Some text2 So I want to declare a variable in XSLT and make a loop in records and store the previous record ID. IF the previous record id is same, then no need to show the name. But when I declare in the following xslt, I am getting 'The variable or parameter 'xxx' was dublicated within the same scope' error<xsl:output method ="html" media-type="text/html" encoding="iso-8859-1" /> <xsl:template match="Details"> <xsl:variable name="counter" select="0"/> <xsl:for-each select="XMLParentNode/XMLDetailNode/XMLSubDetailNode"> <xsl:if test="$counter = 1"> <p> <b style='text-color:red;'> <xsl:value-of select="NAME" disable-output-

How to declare a table variable in SSIS and then insert rows into it

Hello everyone,   I'll try to explain my problem as clearly as I can: 1)I have an Execute SQL Task that's inside a Foreach Loop Container. 2)Inside the Execute SQL Task, I have an int OUTPUT column. 3)With each iteration, I need to insert the data from the INT output colum into a table variable. 4)After the Foreach Loop finishes the iterations, I need to use the table variable to create a report based on the data inside.   My question is the following: How can I declare a table variable so that I can do all of the above?   Thank you, CostinP

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?

can't change varchar(13) back to a datetime.

SQL Server 2005  I had a date field that was acting weird.  I decided it didn't really need to be a datetime type and changed it to a varchar(13) which it happily let me do.  Opps, I really do want it to be a date time field...  But now it won't let me take it back.  Oddly, it also won't let me change the data in the field, or delete the data in the field.  When I try to change the data I get the following... String or binary data would be truncated.  The statement has been terminated. When I try to modify the field I get the following. first i get a warning... - Warning: Data might be lost converting column 'EODate' from 'nchar(13)'. then i get an error when I say yes 'ExecutiveOrder' table - Unable to modify table.   Arithmetic overflow error converting expression to data type datetime. The statement has been terminated. Ok, I'm not the sharpest tool in the shed...  Any suggestions?  Thanks for your help.

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.

auto increment of primary key which is varchar


hi everyone ...

i  was given a form in which empid is varchar  and i m supposed to auto increment it using a  function and calling it ....

i have never done this. plz help me out the way .

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