Variable In DDL Statement In Procedure

Apr 15, 2008

Create table tbl(title nvarchar(40))

Create procedure df_bppr @de nvarchar(30)
As
Begin
Declare @sstr nvarchar(500)
Set @sstr = N'Alter Table tbl Add Constraint df_title Default '+ @de + ' For title'
Exec sp_executesql @sstr, @de
End

Execute df_bppr @de = 'NoTitle'

******************

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'NoTitle'.
Msg 128, Level 15, State 1, Line 1
The name "NoTitle" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

--------------------

I created the procedure without any error. But when i execute the procedure it shows the above error

I want to create a proc which dynamically change the default value for more than one column with same default value.

Vijai

View 6 Replies


ADVERTISEMENT

Select Statement As Input Variable In Stored Procedure?

May 6, 2015

Is it possible to have an entire sql select statement as the input variable to a stored procedure? I want the stored procedure to execute the select statement.

ie.

exec sp_SomeFunc 'select * from table1 where id=1'

It may sound weird, but I have my reason for wanting to do it this way. Is this possible? if so, how do I implement this inside the stored procedure?

View 4 Replies View Related

Can't TABLE Variable Be Used In EXEC Statement In Stored Procedure

Feb 8, 2008

Hi

I've used a temporary table in the stored procedure

I've created it as DECLARE @Temp TABLE (id INT)

in select clause I've used this temp table through the joins..

But I've also used a varchar variable to store my criteria...

which I'm building in the stored procedure

so inorder to use it in the where clause I used

EXEC ('SELECT ....................


FROM @Temp T

LEFT OUTER JOIN ...
LEFT OUTER JOIN ...
WHERE ' + @Criteria )


Unfortunately this is not working.
Giving the errror


Must declare the variable '@TempT'.

I had to use Temporary table using #, which I feel is really waste of memory...

Is there a way by which I can use my Table variable in the EXEC statement.

Thanks In advance

View 4 Replies View Related

SSIS: Problem Mapping Global Variables To Stored Procedure. Can't Pass One Variable To Sp And Return Another Variable From Sp.

Feb 27, 2008

I'm new to SSIS, but have been programming in SQL and ASP.Net for several years. In Visual Studio 2005 Team Edition I've created an SSIS that imports data from a flat file into the database. The original process worked, but did not check the creation date of the import file. I've been asked to add logic that will check that date and verify that it's more recent than a value stored in the database before the import process executes.

Here are the task steps.


[Execute SQL Task] - Run a stored procedure that checks to see if the import is running. If so, stop execution. Otherwise, proceed to the next step.

[Execute SQL Task] - Log an entry to a table indicating that the import has started.

[Script Task] - Get the create date for the current flat file via the reference provided in the file connection manager. Assign that date to a global value (FileCreateDate) and pass it to the next step. This works.

[Execute SQL Task] - Compare this file date with the last file create date in the database. This is where the process breaks. This step depends on 2 variables defined at a global level. The first is FileCreateDate, which gets set in step 3. The second is a global variable named IsNewFile. That variable needs to be set in this step based on what the stored procedure this step calls finds out on the database. Precedence constraints direct behavior to the next proper node according to the TRUE/FALSE setting of IsNewFile.


If IsNewFile is FALSE, direct the process to a step that enters a log entry to a table and conclude execution of the SSIS.

If IsNewFile is TRUE, proceed with the import. There are 5 other subsequent steps that follow this decision, but since those work they are not relevant to this post.
Here is the stored procedure that Step 4 is calling. You can see that I experimented with using and not using the OUTPUT option. I really don't care if it returns the value as an OUTPUT or as a field in a recordset. All I care about is getting that value back from the stored procedure so this node in the decision tree can point the flow in the correct direction.


CREATE PROCEDURE [dbo].[p_CheckImportFileCreateDate]

/*

The SSIS package passes the FileCreateDate parameter to this procedure, which then compares that parameter with the date saved in tbl_ImportFileCreateDate.

If the date is newer (or if there is no date), it updates the field in that table and returns a TRUE IsNewFile bit value in a recordset.

Otherwise it returns a FALSE value in the IsNewFile column.

Example:

exec p_CheckImportFileCreateDate 'GL Account Import', '2/27/2008 9:24 AM', 0

*/

@ProcessName varchar(50)

, @FileCreateDate datetime

, @IsNewFile bit OUTPUT

AS

SET NOCOUNT ON

--DECLARE @IsNewFile bit

DECLARE @CreateDateInTable datetime

SELECT @CreateDateInTable = FileCreateDate FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName

IF EXISTS (SELECT ProcessName FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName)

BEGIN

-- The process exists in tbl_ImportFileCreateDate. Compare the create dates.

IF (@FileCreateDate > @CreateDateInTable)

BEGIN

-- This is a newer file date. Update the table and set @IsNewFile to TRUE.

UPDATE tbl_ImportFileCreateDate

SET FileCreateDate = @FileCreateDate

WHERE ProcessName = @ProcessName

SET @IsNewFile = 1

END

ELSE

BEGIN

-- The file date is the same or older.

SET @IsNewFile = 0

END

END

ELSE

BEGIN

-- This is a new process for tbl_ImportFileCreateDate. Add a record to that table and set @IsNewFile to TRUE.

INSERT INTO tbl_ImportFileCreateDate (ProcessName, FileCreateDate)

VALUES (@ProcessName, @FileCreateDate)

SET @IsNewFile = 1

END

SELECT @IsNewFile

The relevant Global Variables in the package are defined as follows:
Name : Scope : Date Type : Value
FileCreateDate : (Package Name) : DateType : 1/1/2000
IsNewFile : (Package Name) : Boolean : False

Setting the properties in the "Execute SQL Task Editor" has been the difficult part of this. Here are the settings.

General
Name = Compare Last File Create Date
Description = Compares the create date of the current file with a value in tbl_ImportFileCreateDate.
TimeOut = 0
CodePage = 1252
ResultSet = None
ConnectionType = OLE DB
Connection = MyServerDataBase
SQLSourceType = Direct input
IsQueryStoredProcedure = False
BypassPrepare = True

I tried several SQL statements, suspecting it's a syntax issue. All of these failed, but with different error messages. These are the 2 most recent attempts based on posts I was able to locate.
SQLStatement = exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
SQLStatement = exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output

Parameter Mapping
Variable Name = User::FileCreateDate, Direction = Input, DataType = DATE, Parameter Name = 0, Parameter Size = -1
Variable Name = User::IsNewFile, Direction = Output, DataType = BYTE, Parameter Name = 1, Parameter Size = -1

Result Set is empty.
Expressions is empty.

When I run this in debug mode with this SQL statement ...
exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the following error message appears.

SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.

Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Compare Last File Create Date

Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "MyPackage.dtsx" finished: Failure.

When the above is run tbl_ImportFileCreateDate does not get updated, so it's failing at some point when calling the procedure.

When I run this in debug mode with this SQL statement ...
exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the tbl_ImportFileCreateDate table gets updated. So I know that data piece is working, but then it fails with the following message.

SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.

Error: 0xC001F009 at GLImport: The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date

Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "MyPackage.dtsx" finished: Failure.

The IsNewFile global variable is scoped at the package level and has a Boolean data type, and the Output parameter in the stored procedure is defined as a Bit. So what gives?

The "Possible Failure Reasons" message is so generic that it's been useless to me. And I've been unable to find any examples online that explain how to do what I'm attempting. This would seem to be a very common task. My suspicion is that one or more of the settings in that Execute SQL Task node is bad. Or that there is some cryptic, undocumented reason that this is failing.

Thanks for your help.

View 5 Replies View Related

Cannot Set A Variable From A Select Statement That Contains A Variable??? Help Please

Oct 4, 2006

I am trying to set a vaiable from a select statement

DECLARE @VALUE_KEEP NVARCHAR(120),

@COLUMN_NAME NVARCHAR(120)



SET @COLUMN_NAME = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CONTACTS' AND COLUMN_NAME = 'FIRSTNAME')



SET @VALUE_KEEP = (SELECT @COLUMN_NAME FROM CONTACTS WHERE CONTACT_ID = 3)



PRINT @VALUE_KEEP

PRINT @COLUMN_NAME



RESULTS

-------------------------------------------------------------------------------------------

FirstName <-----------@VALUE_KEEP

FirstName <-----------@COLUMN_NAME



SELECT @COLUMN_NAME FROM CONTACTS returns: FirstName

SELECT FirstName from Contacts returns: Brent



How do I make this select statement work using the @COLUMN_NAME variable?

Any help greatly appreciated!

View 2 Replies View Related

Using C# Variable With SQL Statement

Nov 12, 2007

Greetings everyone,
I am trying to use a c# string with an SQL statement in a data adapter (.NET 03)
The code works fine and I have a variable called : string test = ..... that takes the needed values. I just need to implement this string in the sql statement. I tried adding this to my query but I only got an empty row: 
WHERE (login = '" & test &  "')
WHERE (login = '" + test +  "')
 any ideas?
PS: If I change to something like WHERE (login = 'abcdef') I get  a result meaning there's something wrong with the way I am putting the variable in the sql query.
 Again, I am not putting the string in a normal query in my .cs code. this is happening by right clicking the data adapter and configuring the sql statement in the designer window
THANKS!

View 8 Replies View Related

Variable In An Sql Statement

Dec 14, 2005

Hi,

I've created an sql statement:
select *
from fin_installment
where key_construction = (select ser_construction from fin_construction where key_contract = ' " & variable & " ') order by int_serial

which is in an Dataset's TableAdapter.
This variable receives its value during the form init and it is an integer.
When I start the page the folowing error message is displayed:

" An error has occurred during report processing.
Exception has been thrown by the target of an invocation.
Conversion failed when converting the varchar value ' " & azonosito & " ' to data type int. "

So my question is that how can I use variables in sql statement in dataset?

View 3 Replies View Related

Using Variable In LIKE Statement

Dec 13, 2007

Hi, I am trying to use a variable inside a LIKE statement, but it is not working as expected. It will not give a error, but it shows no results while it does show results if I replace the variable with the normal string within the LIKE statement. Here is my code:


Code:

-- this example returns results
SELECT whatever
FROM mytable
WHERE whatever LIKE 'blah%';




Code:

-- this example returns no results
DECLARE @test VARCHAR;
SET @test='blah%';

SELECT whatever
FROM mytable
WHERE whatever LIKE @test;



Any ideas why the version using the variable would not work?

Patrick

View 3 Replies View Related

USE Statement With A Variable?

Jun 17, 2004

I'm having some trouble modifing a script to save me tons of work. The script if from Microsoft, and it is used as step 3 in a 6 step process to move MS Great Plains users from one server to another. Anyway, the script runs on only 1 company database at a time, and for most Great Plains environments there would only be 1 or 2 company DBs. But I am administering in an ASP environment and we have over 30 company DBs to move. So, I though I would adapt thier script to iterate over each company DB to do the work (rather than creating 30 separate scripts). So I wrapped their loop with my loop to do the iteration. The problem is that T-SQL will not let me use a variable in a USE statement. I've tried to remove the USE statements, but that added a lot of complexity in the internal loop. What is the best way to do this?

Here is the modified code:

/*
** Drop_Users_Company.sql
**
** This script will remove all users from the DYNGRP in the company database
** specified. It will then drop the DYNGRP and readd the DYNGRP to the company.
** It will then add all users back to the DYNGRP based on the SY60100 table.
** NOTE: You will need to replace %Companydb% with the company database
** name.
*/
/* Instead of replacing %Companydb% (in each USE statement) with the name of the
single company database that this script is supposed to work on, I've added
@cCompany to hold the company DB name through each iteration of the outside
cursor/while loop.
*/

declare @cCompany sysname/* ADDED BY ME FOR THE OUTSIDE LOOP */
declare @cStatement varchar(255)/* Misc exec string */
declare @DynDB varchar(15)/* DB Name exec string */
declare @DYNGRPgid int/* Id of DYNGRP group */

/*
** Loop through all company databases, emptying the DYNGRP group.
*/
SET QUOTED_IDENTIFIER OFF

use DYNAMICS

/* Select all of the Great Plains database names from the DB_Upgrade table, where the DB names are conviently stored */
declare C_cursor CURSOR for select db_name from DYNAMICS..DB_Upgrade where db_name not in ('DYNAMICS')

OPEN C_cursor
FETCH NEXT FROM C_cursor INTO @cCompany
WHILE (@@FETCH_STATUS <> -1)
begin
use @cCompany
select @DYNGRPgid = (select gid from sysusers where name = 'DYNGRP')

declare G_cursor CURSOR for select "sp_dropuser [" + name+"]" from sysusers
where gid = @DYNGRPgid and name <> 'DYNGRP'

set nocount on

OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
EXEC (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor
/*
** Do not delete the group to attempt to preserve the permissions already
** granted to it.
*/
use @cCompany
if exists (select gid from sysusers where name = 'DYNGRP')
begin
exec sp_dropgroup DYNGRP
end
/*
** Recreate the DYNGRP group in all company databases.
*/
use @cCompany
if not exists (select name from sysusers where name = 'DYNGRP')
begin
exec ("sp_addgroup DYNGRP")
end

end
DEALLOCATE C_cursor

______________________________________
Thanks for any help you have.

View 1 Replies View Related

My Variable In Sql Statement

Aug 11, 2006

Declare @MyCode nvarchar(20);
Set @MyCode='ABC'
set @int_rowcount=(SELECT count(hoten) FROM @MyCode)
I run it but still errors !
How can i implement above statement ?
Thank you very much !

View 6 Replies View Related

USE Statement With Variable

Jul 20, 2005

Hi,I am doing a really simple test with SQL Server 7.0:Using the Query AnalyzerLogged as saLocated in master database#1 USE Test#2 EXEC('USE Test')#1 => the database context is switched to Test#2 => the database is NOT switched???

View 2 Replies View Related

SQL Statement In Variable

Sep 3, 2007

Hello Everyone,
I wanted to pass a SQL statement thru a variable, and use that variable in my source component.

SELECT CLINIC_SUK, CLINIC_CODE, CLINIC_DESC, CLINIC_ARABIC, Load_DT
FROM DIM_CLINIC
where load_dt > ?

I had created a variable with my SQL statement and mapped that variable in my source component.
Its giving me some error.

Parameter Information cannot be derived from SQL statement. Set parameter information before preparing command.


Please do inform me about the solution for having a parameter in my source SQL Statement.

View 3 Replies View Related

SQL Statement With Variable.

Apr 4, 2008

Hi ,

I am testing a very simple query that use variable for sort direction and sort expression



DECLARE

@SortExp nvarchar(256),

@SortDir nvarchar(10)


Set @SortExp = 'curTime'

Set @SortDir = 'DESC'


Select * from table
where recID < 20
order by @SortExp @SortDir

and i got this error...


The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.


Is there anyway to do this task.

Thanks

Ddee

View 3 Replies View Related

Can Variable Be Used In SQL UPDATE Statement In VB.NET

Dec 16, 2007

Hy, i have this problem in vb.net:
I must use a variable in SQL UPDATE statement, after SET statement, and i'm getting error. This is that line of code:
Dim variable_name As StringDim variable As Integer
Dim sqlString As String = ("UPDATE table_name SET " variable_name " = " & variable & " WHERE UserID = '" & UserID & "'")Dim cmdSqlCommand As New SqlCommand(sqlString, conConnetion)
cmdSqlCommand.ExecuteNonQuery()
 
When I don't use a variable after SET statement, everything work fine. This code works fine:
 Dim variable As Integer
Dim sqlString As String = ("UPDATE table_name SET column_name = " & variable & " WHERE UserID = '" & UserID & "'")Dim cmdSqlCommand As New SqlCommand(sqlString, conConnetion)
cmdSqlCommand.ExecuteNonQuery()
 
Please, if someone can help me in this...thanks..

View 2 Replies View Related

Passing Variable To Sql Statement

Apr 28, 2008

could anyone please help me to resolve this issue?
here's my sql query which retrieve last 3 month data
t.execute(SELECT * tbl1 where nmonth >= datepart(mm,DATEADD(month, -3, getdate())) or nmonth <=datepart(mm,getdate()) and empno='"+emppip+"'")
now instead of passing 3 in this query(datepart(mm,DATEADD(month, -3, getdate())) )
i need to pass a variable to retrieve data based on user requirements.
i tried this way,
dim mno as n
mno=4
t.execute(SELECT * tbl1 where nmonth >= datepart(mm,DATEADD(month, -'"+mno+"', getdate())) or nmonth <=datepart(mm,getdate()) and empno='"+emppip+"'")
its not working.
can i achieve this using stored procedure? or can i directly pass a variable to sql synatax?
thanks for any help   

View 8 Replies View Related

Use A Table Name As A Variable In The From Statement

Oct 26, 2005

I'm curious if anyone knows the correct way to do this pseudo-statement correctly?  I want to create a stored procedure in which I send it the table name of the table I want to query.declare @tableName varchar(500)set @tableName = 'PortfolioPreferenceOwnership' select * from @tableName

View 4 Replies View Related

Using A Variable In An Update Statement

Jan 2, 2001

I am having difficulties with some sql syntax with sql server 2000.
I am trying to write code to update a column in which the name of it is unknown. At run time, I am able to set a variable equal to the correct column name but in doing so, treats the value as a String.
Ex.
Declare @varA varchar(12)
select @varA = (select top 1 Value from #temp)

Update TableX
set @varA = y.ColTest
from TableX x, TableY y
where x.Colid = y.Colid

It sets the variable = to the last value from TableX.ColTest
I want the Update statement to update the value for the Variable which
represents the correct column to update.

Any ideas?

Thanks,

Daniel

View 1 Replies View Related

Want To Use A Variable In A &#39;use DB&#39; Statement In SQL Script

Jan 18, 2002

Hi,
I want to use a variable in a 'use' statement...
but, I cannot figure out the syntax, nor do I know if it is possible...
Here is an example SQL script:
/*-----------------------------------------------------*/

DECLARE @DataBase varchar(60)
--Declare cursor for all DBs except master, MSDB, Model, tempdb
DECLARE curdb CURSOR for select name from master..sysdatabases
where
name not in ('master', 'MSDB', 'Model','tempdb')for read only

--Open and perform initial fetch
open curdb
fetch curdb into @DataBase

--While there are databases to process, process each DB
While @@fetch_status = 0

PRINT @DataBase
use + ' ' + @database --or,
use @database

fetch curdb into @DataBase

end

/*------------------------------*/
Thanks,
Michael

View 1 Replies View Related

USE @dbname (Use Statement With Variable)

Jun 12, 2002

Hi

I need to run a stored procedure on each database in my SQL server. I want to have a loop to go through each db.

Is there a way I can run 'Use @dbname', I tried Execute and sp_executesql but it didn't work.

I want to execute the SP withing each db.

Thanks

View 2 Replies View Related

How To Use Transact SQL Variable In A SQL In Statement

Mar 2, 2007

Hi all,

I have been struggling with the below transact sql user defined function. I want to use a transact sql variable in an "in" statement. I don't get any results and I am not sure if I am receiving an error or not.

Code:


DECLARE @myval varchar(50),@username varchar(50)
DECLARE @rolelist varchar(2000)
SET @rolelist = ''
SET @myval = 'user a,user b'
select @myval = ''''+ replace(@myval,',',''',''') + ''''
print @myval

DECLARE User_Cursor CURSOR FOR
select distinct eusername
from euser
where eusername in (@myval)


OPEN User_Cursor

FETCH NEXT FROM User_Cursor INTO @username

SET @myval = @username
SET @rolelist = @username
WHILE @@FETCH_STATUS = 0
BEGIN
SET @rolelist =+ @rolelist + ',' + @username


FETCH NEXT FROM User_Cursor INTO @username
END
CLOSE User_Cursor
DEALLOCATE User_Cursor
print @myval
print 'rolelist' + @rolelist
GO



I am at a loss any suggestions would be greatly appreciated.

View 4 Replies View Related

Variable In CURSOR Sql Statement (was Please Help Me)

Dec 24, 2004

Hi All,

What i am trying to do is concatenate variable "@Where" with CURSOR sql statement,inside a procedure . But, it doesn't seem to get the value for
the @Where. (I tried with DEBUGGING option of Query Analyzer also).

=============================================
SET @Where = ''
if IsNull(@Input1,NULL) <> NULL
Set @Where = @Where + " AND studentid='" + @input1 +"'"

if isnull(@Input2,NULL) <> NULL
Set @Where = @Where + " AND teacherid =' " + @Input2 +"'"

DECLARE curs1 CURSOR SCROLL
FOR SELECT
firstname
FROM
school
WHERE
school ='ABC' + @where
=============================================
Please check my SQL Above and Could somebody tell me how can I attach the VARIABLE with CURSOR sql statement ?

Thanks !

View 3 Replies View Related

Variable In A Select Statement

Sep 6, 2006

Is there anyway to use a variable to define a column in a select statement. I can put the variable in but I'm sure it will be read as a literal instead of the column.

select @column_name from table

View 2 Replies View Related

Set Variable Value In CASE Statement?

Jun 29, 2012

Is it possible to set a value to a variable if a case statement is true?

like this:

CASE
WHEN Utable.type = 2 THEN U.Username2 + (@Uname= 2)
WHEN Utable.type = 3 THEN U.Username3 + (@Uname= 3)

Ive tried above and:

"+ (select @Uname = 2)"
"+ (SET @uname = 2)"
"; SET @uname = 2"
...

and several other things but non works.

variable's = INT
"Utable.type" = INT
"U.Username"2/3 = nvarchar(50)

View 2 Replies View Related

How To Set A Variable In An If Exists Statement

Feb 28, 2008



Hello,

I would like to set a variable within my if exists statement, however SQL is throwing and error stating:


Incorrect syntax near '='.

If I remove the if exists, the query runs fine. Is there a reason why this is not working the way I have it and what suggestions can I use to accomplish what I am trying to do, which is store the ID into the permissionID variable

Here is my code block:



Code Snippet

declare @permissionID int;

if exists(select @permissionID = Id from Permission

where [Description] = 'SettlementReport')






Thanks,
Flea#

View 3 Replies View Related

Passing A Variable To A SQL Statement

Mar 10, 2008

I've been coding a few years and SSIS makes me feel more stupid than any program I've ever used.
I've read BOL and bought a book. Can't say either one has really helped.

I'm still a complete idiot after one week of working with it. I apologize for asking so many stupid questions.

What I'm trying to do now is parameterize a SQL statement.

I have a variable that's a string. I have a DataFlowComponent as a data source. I find references all over the Internet and this forum to something called an "ExecuteSQLTask" but I sure can't figure out what that is. In my toolbox I have data flow sources for DataReader, Excel, Flat Files, Old DB, Raw file, and XML but no Execute Sql Task.

Anyway SELECT * FROM TABLE WHERE COLUMN='Value' in the SQL Command property is simple enough. Now I want 'Value' to be a variable. You know, like in T/SQL DECLARE @Foo VarChar(25). Creating the variable is easy as pie.

I have found at least 10 different examples of specifying variables on the web, all of which claim to be SSIS examples, Is it User::Variable? @[User::Variable]? @Variable?

I want to read rows from a table, with a a variable value in the WHERE clause, and pass them to the fuzzy lookup task.

Is my approach fundamentally flawed in some way?





View 25 Replies View Related

Using A Variable In A GROUP BY Statement

Sep 2, 2007



Hi Experts, I would like to make a stored procedure in my db: I have sql express 2005.
I get Error 164 when creating this procedure:


CREATE PROCEDURE CrossTable


@Variable1 smallint,

@Variable2 smallint,

@Value smallint

AS


BEGIN


SELECT @Variable1, COUNT(@Variable1) AS 'Haufigkeiten'

FROM SurveyData

WHERE @Variable2 = @Value

GROUP BY @Variable1

END

GO


I would like to generate a frequency chart per userdefined-variable (@variable) with a where restriction.

The GROUP BY @Variable1 seems to be problem: is there some workaround in order to use variables in a GROUP BY clause? or how can I write an sql statement which do the same as this procedure (CrossTable) without using the GROUP BY clause?

Thanks a lot for your replies

Greets from Switzerland

Chris

View 3 Replies View Related

Incorrect Sql Statement - Not Reading Variable

Dec 5, 2006

In the funtcion below I am selecting a value from the page -  Request.QueryString("ProjectID") and this is being pulled through correctly - if I debug then ProjectID = Request.QueryString ("ProjectID") does equal the correct value.
However this value is not then working in the following line:
strSQL = "SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [ProjectID]"
This statement is not reading any value for ProjectID and so is selecting all from the table.....
How do I write this statement to pick up the variable above?????
Thanks in advance for your help!
Clare 
 
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim ds As New Data.DataSet
Dim da As Data.SqlClient.SqlDataAdapter
Dim strSQL As String
Dim ProjectID As Int32
ProjectID = Request.QueryString("ProjectID")
strSQL = "SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [ProjectID]"
Dim connString As String = "Data Source=xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx"
da = New Data.SqlClient.SqlDataAdapter(strSQL, connString)
da.Fill(ds)
ds.Tables(0).Columns.Add("imgFile")
 
For Each tempRow As Data.DataRow In ds.Tables(0).Rows
tempRow.Item("imgFile") = ("imgGrab2.aspx?id=" & tempRow.Item("ProjectID"))
Next
ImgGrid3.DataSource = ds
ImgGrid3.DataBind()
 
End Sub

View 12 Replies View Related

Correct SQL Statement For URL Variable Display?

Apr 6, 2007

I have a page that is displaying different movie covers. They each have their respective genres stored in several keys (genre0, genre1, genre2). Now I can search within the movies by changing the url variable movies.aspx?genre=action but if I simply go to movies.aspx, I'm wanting all genres to appear. But for me, the page is blank if I don't specify the genre. So I've had to start filling in the genre All for all movies so I can go to movies.aspx?genre=all but I know this can't be necessary. Is it my SQL statement for the datasource causing the problem? Code is in VB.  <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx"
ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM [Movies] WHERE [genre0] = @strGenre &#13;&#10;OR [genre1] = @strGenre &#13;&#10;OR [genre2] = @strGenre">
<SelectParameters>
<asp:QueryStringParameter Name="strGenre" QueryStringField="genre" />
</SelectParameters>
</asp:SqlDataSource>

<asp:DataList ID="DataList1" runat="server" DataKeyField="movieID" DataSourceID="SqlDataSource1" RepeatColumns="5" RepeatDirection="Horizontal" CellPadding="5" BorderStyle="None" BorderWidth="0px">
<ItemTemplate>
<asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl='<%# "movie.aspx?id=" & Eval("movieID") %>'>
<asp:Image ID="Image1" runat="server" ImageUrl='<%# Eval("imageURL", "{0}") %>' BorderStyle="None" /><br />
</asp:HyperLink>
</ItemTemplate>
</asp:DataList>  

View 2 Replies View Related

Variable For The Table Name In A SELECT Statement.

Apr 23, 2007

Hi,I'm trying to dynamically assign the table name for a SELECT statement but can't get it to work. Given below is my code: SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE GetLastProjectNumber (@DeptCode varchar(20))
AS
BEGIN TRANSACTION
SET NOCOUNT ON

DECLARE @ProjectNumber int
SET @ProjectNumber = 'ProjectNumber' + REPLACE(CONVERT(char,@DeptCode),'.','')
SELECT MAX(@ProjectNumber)
FROM 'tbl_ProjectNumber' + REPLACE(CONVERT(char,@DeptCode),'.','');
END TRANSACTION  Basically, I have a bunch of tables which were created dynamically using the code from this post and now I need to access the last row in a table that matches the supplied DeptCode. This is the error I get:Msg 102, Level 15, State 1, Procedure GetLastProjectNumber, Line 29Incorrect syntax near 'tbl_ProjectNumber'. Any help would be appreciated.Thanks. 

View 3 Replies View Related

Filtering SQL Statement By Server Variable

Apr 27, 2007

Hi
 Hope somebody can help me with a problem that i am having. I have the following piece of code
<%
Dim sqlConn As SqlConnection
Dim sqlCmd As SqlCommand
Dim sqlReader As SqlDataReader
 
sqlConn = New SqlConnection("server=myserver;database=aspnet;uid=username;pwd=password")
sqlCmd = New SqlCommand("SELECT SamAccount, FirstName, Surname, FirstName + N' ' + Surname+' Logged In ' AS UserLoggedIn FROM tblUsers WHERE (SamAccount = " + Request.ServerVariables("logon_user") + ")", sqlConn)
sqlConn.Open()
sqlReader = sqlCmd.ExecuteReader()
%>
<%
 
Dim strPCNAme As String
 
sqlReader.Read()
 
strPCNAme = sqlReader("UserLoggedIn")
Response.Write(strPCNAme)
 
%>
 The bit i have i have underlined and made bold throws up the error. Basically what i am trying to do is to filter the the SQL statement by the server variable logon user. Everthing works fine until the point that i put the where clase in.
 Can anybody help please?
 Thanks
Marcus

View 2 Replies View Related

Using A Variable For Tablename In Select Statement?

Sep 19, 2007

I have a stored procedure that accepts the table name as a parameter. Is there anyway I can use this variable in my select statement after the 'from' clause. ie "select count(*) from @Table_Name"?
When I try that is says "Must declare the table variable @Table_Name". Thanks!

View 1 Replies View Related

Calling Variable Inside T-SQL Statement

Oct 4, 2007

Can someone please take a quick look at this and tell me what I'm doing wrong I'm sure it's something simple.  I'm a little new to stored procedures but I've been using SQL and T-SQL for quite some time, I've just always used inline queries with my ASP.  This procedure needs to be run monthly by me or another person I grant access to and will update sales information that our sales staff will be paid commission on.  I need to supply the start date and and end date for the query and it will pull this information from our business system which is hosted remotely by a third party and pull it into our local SQL server where we can run commission reports against it.  (I hope this is enough information you can understand where I'm trying to go with this).  I know my problem right now lies in how I'm trying to call the variable inside of my T-SQL.  Any help is appreciated.  This is an old Unix system and it stores the date as YYYYMMDD as numeric values incase someone wonders why I have dimed my dates as numeric instead of as datetime =)
I'm using a relativity client to create an ODBC connection to the UNIX server and then using a linked server to map a connection in SQL this is the reason for the OpenQuery(<CompanyName>
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: XXXXXXXXXXXXX
-- Create date: 10/4/2007
-- Description: This proc is designed to pull all CSA
-- part sales from XXXXXX business system and upload them
-- into the local XXXXXXXX Database for commission reporting
-- =============================================CREATE proc usp_CSAPartsSalesUpdate@date1 int, @date2 int
As
INSERT INTO CSAPartsSales ( CSA, CustomerNumber, CustomerName, Location, InvoiceNumber, InvoiceDate, InvoiceAmount )
SELECT SalesRoute, HInvCust, CustOrCompName, HInvLoc, HInvNo, HInvDate, HInvAmt From OpenQuery(<CompanyName>, 'Select CPBASC_All.SalesRoute, PMINVHST.HInvCust, CPBASC_All.CustOrCompName, PMINVHST.HInvLoc, PMINVHST.HInvNo, PMINVHST.HInvDate, PMINVHST.HInvAmtFROM PMINVHST INNER JOIN CPBASC_All ON PMINVHST.HInvCust = CPBASC_All.CustomerNo
WHERE (((PMINVHST.HInvAmt)<>0) AND ((PMINVHST.HInvDate)>=''' + @date1 + ''' And (PMINVHST.HInvDate)<=''' + @date2 + ''') AND ((Trim([CPBASC_All].[SalesRoute]))<>'''' And (Trim([CPBASC_All].[SalesRoute]))<>''000''))')
 
In this example date1 will be equal to 20070901 and date2 will be equal to 20070930 so I can pull all CSA sales for the month of September.
This is the error message I get when I try to create the proc:
Msg 102, Level 15, State 1, Procedure usp_CSAPartsSalesUpdate, Line 17
Incorrect syntax near '+'.
~~~ Thanks All~~~
 

View 9 Replies View Related

Using A Variable To Specify Table Column In Sql Statement??

Feb 6, 2008

Hello everyone,
I'm still quite new ASP.net, Visual Web Developer 2008, and SQL. It has been a fun learning experience so far. Anyways, the site I am designing needs to allow its users to extensively search several different databases (MS SQL databases).  I have followed many of the tutorials and have found it rather easy to add table adapters, gridviews and other data features that use basic SQL Select statements. One of the major database tables contains several columns which I would like to include as a search parameters from a drop down list. I was wondering if there is any way I can write a select which will pass a variable to be used as a column name to the statement?  For example:
SELECT DATE, GAME, EXACT, @COLNAMEFROM HistoryWHERE @COLNAME = @SOMEVARIABLE
This obviously doesnt work, but thats the gist of what I want to do. Any suggestions? I need this to be simple as possible, Most everything I'm doing is done through the visual design mode. Im still slow to learn C# and apply it in the codebehind files unless I have very detailed step by step instuctions.
Thanks
Scott

View 15 Replies View Related







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