creating tables

Wondering if I while I create a table I can choose certain values a field can have?
Ex.
CREATE TABLE test
(
Subject_code char(20) NOT NULL,
Student_id char(20) NOT NULL,
Result GOOD or BAD or SUPERGOOD (this is just an example)
)

If a field can have 2 values I can use bit (boolean). But is there a way to do this with several values?Normally you would use a check constraint. Like this:
CREATE TABLE dbo.ExampleTable (
EntryID int NOT NULL IDENTITY(1,1),
Result varchar(32) NOT NULL
CONSTRAINT ExampleTable_Result_CK CHECK (
Result IN ('GOOD', 'BAD', 'SUPERGOOD'))
)

INSERT INTO ExampleTable (Result) VALUES ('GOOD')
INSERT INTO ExampleTable (Result) VALUES ('BAD')
INSERT INTO ExampleTable (Result) VALUES ('UGLY')

SELECT * FROM ExampleTable
Originally posted by buggirl
Wondering if I while I create a table I can choose certain values a field can have?
Ex.
CREATE TABLE test
(
Subject_code char(20) NOT NULL,
Student_id char(20) NOT NULL,
Result GOOD or BAD or SUPERGOOD (this is just an example)
)

If a field can have 2 values I can use bit (boolean). But is there a way to do this with several values?sql

creating procedures

I have seen developers create procedures by declaring the variables and
others declaring the variables and setting them to null like below.
Is one way better that another?
CREATE PROCEDURE [dbo].[testsp_InsertIssuerDetails]
@.issuerId int output,
@.companyId int = NULL,
@.issuerName varchar(60) = NULL,
@.dateAdded datetime = NULL,
@.dateModified datetime = NULL,
@.statusDate datetime = NULL,
@.modifiedBy smallint = NULL ....
thanks
MeenalWhen declaring parameters of a procedure the declaration can include a
default value for each parameter. This is what your example shows. This way
the parameters become optional.
On the other hand, the declaration of a variable cannot include a default
value.
ML
http://milambda.blogspot.com/|||If a parameter to a stored procedure has a default assigned, the
parameter is optional when called. If no default is defined, the
parameter is required, and if not provided it will fail:
exec sp_helpindex
Server: Msg 201, Level 16, State 4, Procedure sp_helpindex, Line 0
Procedure 'sp_helpindex' expects parameter '@.objname', which was not
supplied.
Roy Harvey
Beacon Falls, CT
On Tue, 27 Jun 2006 00:48:47 -0400, "meenal"
<meenal_dhody@.hotmail.com> wrote:

>I have seen developers create procedures by declaring the variables and
>others declaring the variables and setting them to null like below.
>Is one way better that another?
>CREATE PROCEDURE [dbo].[testsp_InsertIssuerDetails]
> @.issuerId int output,
> @.companyId int = NULL,
> @.issuerName varchar(60) = NULL,
> @.dateAdded datetime = NULL,
> @.dateModified datetime = NULL,
> @.statusDate datetime = NULL,
> @.modifiedBy smallint = NULL ....
>thanks
>Meenal
>|||IF they are defined in the CREATE statement, what 'looks' like variables are
really known as 'Parameters'. Variables are defined in the stored procedure
body with the DECLARE statement.
It's often easy to confuse Parameters and Variables.
When Parameters are provided values when they are defined, two things are no
w possible. First, it is not necessary to provide a value for that Parameter
when the stored procedure is executed. Second, it may be that a particular
value 'should' be used it the stored procedure if no value of offered.
In Query Analyzer, try the following example:
CREATE PROCEDURE dbo.MyTest
( @.CheckValue int = 0
, @.DateInput datetime = NULL
)
AS
IF @.DateInput IS NULL
SET @.DateInput = getdate()
IF @.CheckValue = 0
PRINT 'Choice Number 0 Selected on ' + cast( @.DateInput AS varchar(11) )
ELSE IF @.CheckValue = 1
PRINT 'Choice Number 1 Selected on ' + cast( @.DateInput AS varchar(11) )
ELSE
PRINT 'Choice Number ' + cast( @.CheckValue AS varchar(5) ) + ' Selected on '
+ cast( @.DateInput AS varchar(11) )
GO
EXECUTE dbo.MyTest
EXECUTE dbo.MyTest @.CheckValue = 1
EXECUTE dbo.MyTest @.CheckValue = 25, @.DateInput = 'jan 1 2005'
--(Note: it is a good practice to use name=value pairs when calling stored p
rocedures and passing parameters.
--It makes your intention very clear, and protects against future changes to
the stored procedure.)
-- Clean up when finished
--DROP PROCEDURE MyTest
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"meenal" <meenal_dhody@.hotmail.com> wrote in message news:uathBVamGHA.2112@.TK2MSFTNGP04.phx
.gbl...
>I have seen developers create procedures by declaring the variables and
> others declaring the variables and setting them to null like below.
>
> Is one way better that another?
>
> CREATE PROCEDURE [dbo].[testsp_InsertIssuerDetails]
> @.issuerId int output,
> @.companyId int = NULL,
> @.issuerName varchar(60) = NULL,
> @.dateAdded datetime = NULL,
> @.dateModified datetime = NULL,
> @.statusDate datetime = NULL,
> @.modifiedBy smallint = NULL ....
>
> thanks
>
> Meenal
>
>

Creating Custom code with DBNull and Report Parameters

How can I force report parameter to accept a null value. I tried using the check box for null values to no avail. I am creating a custom code to get around this problem. I used the simple IIf statement below:

=IIF (Parameters!MarketID.Value = "None", IsDBNull.Value, Parameters!MarketID.Value)

...I was getting an error message like this:

The value expression for the report parameter ‘MarketID’ contains an error: [BC30455] Argument not specified for parameter 'value' of 'Public Shared Function IsDBNull(value As Object) As Boolean'.

How can I frame the IIf statement?

Thx in advance

You could try replacing IsDBNull.Value with System.DBNull.Value